Google Sheets pulling wrong price for TLT
Moderator: Global Moderator
- dualstow
- Executive Member
- Posts: 14225
- Joined: Wed Oct 27, 2010 10:18 am
- Location: synagogue of Satan
- Contact:
Google Sheets pulling wrong price for TLT
At least on my spreadsheet, it is. For a few days now.
So is google finance.
So is google finance.
RIP Marcello Gandini
- Mark Leavy
- 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
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.
- Cortopassi
- 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
I see it too. Stopped updating EOD Tuesday. Perfect for helping to figure end of year results!
- dualstow
- 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
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.
Code: Select all
=GoogleFinance(".inx","price")
although the spot price of gold from Apmex is now back.
Seems like something is always broken somewhere.
RIP Marcello Gandini
- Cortopassi
- 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
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.
=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.
- dualstow
- 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
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.
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"
Last edited by dualstow on Tue Jan 03, 2017 11:04 am, edited 1 time in total.
RIP Marcello Gandini
- Cortopassi
- 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
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)
=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)
- dualstow
- 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
Apmex gold: I've been using the exact same code. It just seemed broken for a couple days.
RIP Marcello Gandini
- Mark Leavy
- 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
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
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"))
Code: Select all
=importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",A1,"&f=p2"))
Bid Prices on individual coins
Code: Select all
=importhtml("http://www.coloradogold.com/htsell.php","table",5)
Code: Select all
=importhtml("http://www.ajpm.com/gold-bullion.html","table",0)
Notes and Bonds
Code: Select all
=importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0)
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.
- dualstow
- 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
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.
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
- Mark Leavy
- 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
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.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.
- Cortopassi
- 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
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);
}
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);
}
- dualstow
- 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
Thank you for that! Man, need to keep this forum going.Mark Leavy wrote: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.dualstow wrote:~ Or maybe the table doesn't include 6-mo bills?
RIP Marcello Gandini
- dualstow
- 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
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.
I suppose it's kind of silly to follow the value of a six-mo bill anyway.
RIP Marcello Gandini
- Kriegsspiel
- Executive Member
- Posts: 4052
- Joined: Sun Sep 16, 2012 5:28 pm
Re: Google Sheets pulling wrong price for TLT
How do you make these cells point at the CUSIP you're trying to price?Mark Leavy wrote: For Treasuries:
Notes and BondsT-BillsCode: Select all
=importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0)
Code: Select all
=importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",3)
- dualstow
- 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
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.Kriegsspiel wrote:How do you make these cells point at the CUSIP you're trying to price?
The tab with all your holdings has a cell like this:
Code: Select all
=vlookup(date(2040,11,15),wsj!A200:C886,3,FALSE)
- 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
RIP Marcello Gandini
- Mark Leavy
- 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
I do exactly what dualstow does.
-
- Executive Member
- Posts: 1102
- Joined: Fri Jan 06, 2012 9:04 am
Re: Google Sheets pulling wrong price for TLT
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.
Code: Select all
=vlookup(date(2040,11,15),importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0),3,FALSE)
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.
- dualstow
- 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
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'.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.
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
-
- Executive Member
- Posts: 1102
- Joined: Fri Jan 06, 2012 9:04 am
Re: Google Sheets pulling wrong price for TLT
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)
- dualstow
- 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
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.
Small money, but a good learning exercise. I've only done bonds & notes up until today.
RIP Marcello Gandini
-
- Executive Member
- Posts: 521
- Joined: Mon Aug 24, 2015 3:12 pm
Re: Google Sheets pulling wrong price for TLT
I posted about this in another thread:
http://gyroscopicinvesting.com/forum/vi ... 07#p157707
Bumping here.
http://gyroscopicinvesting.com/forum/vi ... 07#p157707
Bumping here.
- dualstow
- 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
Great reading through that thread, Jack. I relearned many things, including: I used to own t-bills, lol.Jack Jones wrote:I posted about this in another thread:
http://gyroscopicinvesting.com/forum/vi ... 07#p157707
Bumping here.
RIP Marcello Gandini
-
- Executive Member
- Posts: 521
- Joined: Mon Aug 24, 2015 3:12 pm
Re: Google Sheets pulling wrong price for TLT
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.
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.
Re: Google Sheets pulling wrong price for TLT
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.