Gyroscopic Investing

At least on my spreadsheet, it is. For a few days now.
So is google finance.

I've seen this before with TLT. It lasted a couple of months before correcting. XIV also hasn't been updating for several months now. I haven't seen a solution anywhere. For what it's worth, the historical prices seem to be correct - just not the current price.

I see it too. Stopped updating EOD Tuesday. Perfect for helping to figure end of year results!

This doesn't work today { S&P }

although the spot price of gold from Apmex is now back.

Seems like something is always broken somewhere.

This works, from Yahoo:

=IMPORTDATA("http://finance.yahoo.com/d/quotes.csv?s=TLT&f=sl1c1p2")

Parameters at the end can be found here:

https://productforums.google.com/d/msg/ ... 5Pxe03_i4J

But even the Yahoo quote is off (showing 119.01 now, when it is 119.46). Makes it tough to know what the hell is right!

And Apmex, is showing 1160 or so, yet the google sheet price is 1156, so the data isn't getting refreshed apparently for me at least. Aargh.

Cool, thank you!
Edit: mine just puts "TLT" the ticker, in the cell.
I changed it to just p2 and that works for me. and just 'a' works for ask price.

I am using these now from Apmex, seem "correct"

=Index(ImportHTML("http://www.apmex.com/spotprices/gold-price/","table",1),2,2)

=Index(ImportHTML("http://www.apmex.com/spotprices/silver- ... ,"table",1),2,2)

Apmex gold: I've been using the exact same code. It just seemed broken for a couple days.

Here's what I'm currently using in my Google Sheets:

For equities. (Cell A1 contains the ticker symbol.)
Last Sale Price Percent Change For Gold:
Bid Prices on individual coins Spot Prices on metals For Treasuries:
Notes and Bonds T-Bills

Re: your last line, Mark, I use the same WSJ table. It's been good for a long time.
There's one instrument that's not showing a price, but I think it's simply too new. Bought it at auction last week. Or maybe the table doesn't include 6-mo bills? This is my first.

dualstow wrote:Re: your last line, Mark, I use the same WSJ table. It's been good for a long time.
There's one instrument that's not showing a price, but I think it's simply too new. Bought it at auction last week. Or maybe the table doesn't include 6-mo bills? This is my first.

I updated my post above to add a link for T-Bills. They are in a different table from Bonds and Notes - but on the same WSJ webpage.

I have been doing some searching on updating, because I don't see the prices being updated very often.

Here is a good site on this and how to make a script that forces an update every minute (or whatever you choose)

https://support.geckoboard.com/hc/en-us ... aticupdate

Using this as a basis, I have made it auto update two cells, spot price of gold and change and I do see it working, by the minute!

function getData() {
var queryString = Math.random();
var cellFunction = '=Index(IMPORTHTML("http://www.apmex.com/spotprices/gold-price?' + queryString + '","table",1),2,2)';

SpreadsheetApp.getActiveSheet().getRange('PP 2017!B23').setValue(cellFunction);

var queryString = Math.random();
var cellFunction = '=Index(IMPORTHTML("http://www.apmex.com/spotprices/gold-price?' + queryString + '","table",1),2,3)';

SpreadsheetApp.getActiveSheet().getRange('PP 2017!C23').setValue(cellFunction);
}

Mark Leavy wrote:

dualstow wrote:~ Or maybe the table doesn't include 6-mo bills?

I updated my post above to add a link for T-Bills. They are in a different table from Bonds and Notes - but on the same WSJ webpage.

Thank you for that! Man, need to keep this forum going.

Turns out the problem is that the PDF from treasury lists this particular T-Bill as maturing on 6.29.17, but the table only has 6.30.

I suppose it's kind of silly to follow the value of a six-mo bill anyway.

Mark Leavy wrote: For Treasuries:
Notes and Bonds

Code: Select all

=importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0)

T-Bills

Code: Select all

=importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",3)

How do you make these cells point at the CUSIP you're trying to price?

Kriegsspiel wrote:How do you make these cells point at the CUSIP you're trying to price?

I don't know how Mark does it, but I have a separate tab called wsj in google sheets. Cell A1 gets the formula, and when you paste it in there, the table appears.

The tab with all your holdings has a cell like this: Note the following:
- You manually put in the date. You may list the CUSIP in the leftmost cell for your own info, but the date is what counts in this example. This is for a long bond I own which matures on Nov15, 2040 when I'll have gray to white chest hair.

- wsj is just what I renamed the tab called Sheet2. Make sure you match the name and then add that exclamation point. That ! tells lookup to look at the tab with the wsj name. (As far as I know, you cannot jump to another document entirely. Only to another tab).

- As for those A and C cell numbers, I originally used the numbers supplied by whomever I copied this from.
Sometimes I peek at the table or just change the number to something bigger as the table changes. Right now, I think it goes all the way to F360 in one of my cells. But, the important part is supplying the right date. Vlookup should do the rest of the work.

If you have $8,000 worth of this bond, multiply the price by a cell that says you have 8"shares" and multiply by a constant of 10, just because.
So:
CUSIP and date information ... 8 (shares) e.g. in cell D 23; vlookup code above e.g. in cell H23. Right now it gives a price of $121.44
Cost info to the right in I23. And then in J23, the value of your holdings:
multiplies your 8 "shares"x $121.44 x 10.

I do exactly what dualstow does.

You can simplify things by combining the formulas and doing away with the extra tab like this:
Thanks for sharing this, btw (though my bonds have a different maturity date and I got all excited before remembering that I needed to change the date in the formula!).

Edit: Also, I'm not sure the table index values are correct in your example. They should be 3 for notes and bonds, and 4 for bills. I'm not sure why 0 also seems to work for notes and bonds since the documentation says the index starts at 1. For anyone following along, the table index is the number after "table" and the comma in the importhtml portion of the formula.

flyingpylon wrote:.
They should be 3 for notes and bonds, and 4 for bills. I'm not sure why 0 also seems to work for notes and bonds since the documentation says the index starts at 1. For anyone following along, the table index is the number after "table" and the comma in the importhtml portion of the formula.

It's good you mentioned that. After I copied the code for bills today and could not find the right date (June29) I started playing around with the numbers, but I was changing to the wrong digit, not the one immediately after "table." So thank you! I'll try digit '4'.

Edit: works! Er....it works in that it found the date, but the price doesn't quite make sense. Or I need a new constant to multiply it by.

Yes, the bills table doesn't have as many columns so if you want the "bid" column, that is column 2 not 3. Note the 2 before FALSE in the formula below.

ok. Have to fly tomorrow, but it'll be fun to fix it when I return.
Small money, but a good learning exercise. I've only done bonds & notes up until today.

I posted about this in another thread:

http://gyroscopicinvesting.com/forum/vi ... 07#p157707

Bumping here.

Jack Jones wrote:I posted about this in another thread:

http://gyroscopicinvesting.com/forum/vi ... 07#p157707

Bumping here.

Great reading through that thread, Jack. I relearned many things, including: I used to own t-bills, lol.

Is anyone actually using this table for pricing their treasury bills?

http://wsj.com/mdc/public/page/2_3020-t ... #treasuryB

If so, how do you go from the table to a price? My attempt ($1000 - value_in_bid_column) seems wrong.

Google is missing TLT data in general for the last few days. I like Google's portfolio viewer the best for interface and features, but their historical data is kinda crap. If anyone has another suggestion for a site where I can enter my purchases/sales and it shows me the portfolio over time (not just 1 year periods, but adjustable windows since inception), I'd love to hear it.