Google Sheets pulling wrong price for TLT

Other discussions not related to the Permanent Portfolio

Moderator: Global Moderator

User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Google Sheets pulling wrong price for TLT

Post by dualstow » Fri Dec 30, 2016 10:50 am

At least on my spreadsheet, it is. For a few days now.
So is google finance.
RIP Marcello Gandini
User avatar
Mark Leavy
Executive Member
Executive Member
Posts: 1950
Joined: Thu Mar 01, 2012 10:20 pm
Location: US Citizen, Permanent Traveler

Re: Google Sheets pulling wrong price for TLT

Post by Mark Leavy » Fri Dec 30, 2016 11:05 am

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.
User avatar
Cortopassi
Executive Member
Executive Member
Posts: 3338
Joined: Mon Feb 24, 2014 2:28 pm
Location: https://www.jwst.nasa.gov/content/webbL ... sWebb.html

Re: Google Sheets pulling wrong price for TLT

Post by Cortopassi » Fri Dec 30, 2016 8:24 pm

I see it too. Stopped updating EOD Tuesday. Perfect for helping to figure end of year results!
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Tue Jan 03, 2017 10:43 am

This doesn't work today

Code: Select all

=GoogleFinance(".inx","price")
{ S&P }

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

Seems like something is always broken somewhere. O0
RIP Marcello Gandini
User avatar
Cortopassi
Executive Member
Executive Member
Posts: 3338
Joined: Mon Feb 24, 2014 2:28 pm
Location: https://www.jwst.nasa.gov/content/webbL ... sWebb.html

Re: Google Sheets pulling wrong price for TLT

Post by Cortopassi » Tue Jan 03, 2017 10:52 am

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.
Last edited by Cortopassi on Tue Jan 03, 2017 10:56 am, edited 1 time in total.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Tue Jan 03, 2017 10:54 am

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

Code: Select all

s=TLT&f=p2"
and just 'a' works for ask price.
Last edited by dualstow on Tue Jan 03, 2017 11:04 am, edited 1 time in total.
RIP Marcello Gandini
User avatar
Cortopassi
Executive Member
Executive Member
Posts: 3338
Joined: Mon Feb 24, 2014 2:28 pm
Location: https://www.jwst.nasa.gov/content/webbL ... sWebb.html

Re: Google Sheets pulling wrong price for TLT

Post by Cortopassi » Tue Jan 03, 2017 10:59 am

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)
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Tue Jan 03, 2017 11:05 am

Apmex gold: I've been using the exact same code. It just seemed broken for a couple days.
RIP Marcello Gandini
User avatar
Mark Leavy
Executive Member
Executive Member
Posts: 1950
Joined: Thu Mar 01, 2012 10:20 pm
Location: US Citizen, Permanent Traveler

Re: Google Sheets pulling wrong price for TLT

Post by Mark Leavy » Tue Jan 03, 2017 12:16 pm

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

For equities. (Cell A1 contains the ticker symbol.)
Last Sale Price

Code: Select all

=importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",A1,"&f=l1"))
Percent Change

Code: Select all

=importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",A1,"&f=p2"))
For Gold:
Bid Prices on individual coins

Code: Select all

=importhtml("http://www.coloradogold.com/htsell.php","table",5)
Spot Prices on metals

Code: Select all

=importhtml("http://www.ajpm.com/gold-bullion.html","table",0)
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)
Last edited by Mark Leavy on Tue Jan 03, 2017 1:00 pm, edited 1 time in total.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Tue Jan 03, 2017 12:25 pm

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.
RIP Marcello Gandini
User avatar
Mark Leavy
Executive Member
Executive Member
Posts: 1950
Joined: Thu Mar 01, 2012 10:20 pm
Location: US Citizen, Permanent Traveler

Re: Google Sheets pulling wrong price for TLT

Post by Mark Leavy » Tue Jan 03, 2017 1:02 pm

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.
User avatar
Cortopassi
Executive Member
Executive Member
Posts: 3338
Joined: Mon Feb 24, 2014 2:28 pm
Location: https://www.jwst.nasa.gov/content/webbL ... sWebb.html

Re: Google Sheets pulling wrong price for TLT

Post by Cortopassi » Tue Jan 03, 2017 2:51 pm

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);
}
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Tue Jan 03, 2017 6:23 pm

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.
RIP Marcello Gandini
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Wed Jan 04, 2017 8:33 am

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.
RIP Marcello Gandini
User avatar
Kriegsspiel
Executive Member
Executive Member
Posts: 4052
Joined: Sun Sep 16, 2012 5:28 pm

Re: Google Sheets pulling wrong price for TLT

Post by Kriegsspiel » Wed Jan 04, 2017 9:47 am

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?
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Wed Jan 04, 2017 10:15 am

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:

Code: Select all

=vlookup(date(2040,11,15),wsj!A200:C886,3,FALSE)
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:

Code: Select all

=D23*H23*10
multiplies your 8 "shares"x $121.44 x 10.
RIP Marcello Gandini
User avatar
Mark Leavy
Executive Member
Executive Member
Posts: 1950
Joined: Thu Mar 01, 2012 10:20 pm
Location: US Citizen, Permanent Traveler

Re: Google Sheets pulling wrong price for TLT

Post by Mark Leavy » Wed Jan 04, 2017 10:32 am

I do exactly what dualstow does.
flyingpylon
Executive Member
Executive Member
Posts: 1102
Joined: Fri Jan 06, 2012 9:04 am

Re: Google Sheets pulling wrong price for TLT

Post by flyingpylon » Wed Jan 04, 2017 12:12 pm

You can simplify things by combining the formulas and doing away with the extra tab like this:

Code: Select all

=vlookup(date(2040,11,15),importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0),3,FALSE)
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.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Wed Jan 04, 2017 3:17 pm

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.
RIP Marcello Gandini
flyingpylon
Executive Member
Executive Member
Posts: 1102
Joined: Fri Jan 06, 2012 9:04 am

Re: Google Sheets pulling wrong price for TLT

Post by flyingpylon » Wed Jan 04, 2017 4:02 pm

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.

Code: Select all

=vlookup(date(2017,6,29),importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",4),2,FALSE)
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Wed Jan 04, 2017 4:10 pm

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.
RIP Marcello Gandini
Jack Jones
Executive Member
Executive Member
Posts: 521
Joined: Mon Aug 24, 2015 3:12 pm

Re: Google Sheets pulling wrong price for TLT

Post by Jack Jones » Wed Jan 04, 2017 5:50 pm

I posted about this in another thread:

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

Bumping here.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Google Sheets pulling wrong price for TLT

Post by dualstow » Wed Jan 04, 2017 6:06 pm

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.
RIP Marcello Gandini
Jack Jones
Executive Member
Executive Member
Posts: 521
Joined: Mon Aug 24, 2015 3:12 pm

Re: Google Sheets pulling wrong price for TLT

Post by Jack Jones » Fri Jan 06, 2017 10:19 am

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.
dragoncar
Executive Member
Executive Member
Posts: 1111
Joined: Wed Aug 10, 2011 7:23 pm

Re: Google Sheets pulling wrong price for TLT

Post by dragoncar » Fri Jan 06, 2017 4:47 pm

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.
Post Reply