Excellent, thank you!CaesarVincentius wrote: ↑Fri Jan 01, 2021 2:23 pm I'm pretty sure they changed how it works. Spent a little bit of time fixing my scripts and have a new solution for you.
TSP share price in Google Sheets like GoogleFinance
Re: TSP share price in Google Sheets like GoogleFinance
Re: TSP share price in Google Sheets like GoogleFinance
1) The TSP loan interest rate is the G-Fund rate (https://www.tsp.gov/loan-basics/loan-types-and-terms/)
2) The URL to pull that value is https://secure.tsp.gov/components/CORS/ ... tRate.html
TSP rant: uggg what was TSP thinking? How hard would it have been to retain a url and or parameter value to just pull "last" prices instead of making us muck around with pulling the last few days and then parsing that. It might be small, but it adds up to the load on their servers too. They have all of those get URLs at CORS but decided "naw, no one needs an easy way to pull the most current prices" [sigh] /rant
EDIT: Also if you don't want to be dependent on TSP Folio for returns either there's
https://secure.tsp.gov/components/CORS/ ... turns.html
https://secure.tsp.gov/components/CORS/ ... turns.html
which includes YTD, 1,3,5,10, Lifetime return.
Re: TSP share price in Google Sheets like GoogleFinance
Re: TSP share price in Google Sheets like GoogleFinance
Re: TSP share price in Google Sheets like GoogleFinance
Code: Select all
https://secure.tsp.gov/components/CORS/getSharePrices.html?Linc=1
https://secure.tsp.gov/components/CORS/getSharePrices.html?L2025=1
Code: Select all
https://secure.tsp.gov/components/CORS
- TimeRunner
- Posts: 1939
- Joined: Sat Dec 29, 2012 8:23 pm
- Location: Beach-side, CA
Re: TSP share price in Google Sheets like GoogleFinance
This is a follow-up post to CaesarVincentius' brilliant post quoted above. I'm not sure if TSP changed something between that post and today (Sunday), but I had to modify the formula because the LEN() function wasn't working correctly against what TSP is returning from IMPORTDATA(). I don't guarantee that these slightly modified formulas continue to work or return the correct results, but this is what's working on today's post date:CaesarVincentius wrote: ↑Fri Jan 01, 2021 2:23 pm I'm pretty sure they changed how it works. Spent a little bit of time fixing my scripts and have a new solution for you.
The above should work with the last number being the fund you want:Code: Select all
=index(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today")),len(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today")))+1,1)
2: L Income
3: L 2025
4: L 2030
5: L 2035
6: L 2040
7: L 2045
8: L 2050
9: L 2055
10: L 2060
11: L 2065
12: G Fund
13: F Fund
14: C Fund
15: S Fund
16: I Fund
Today's Price using CaesarVincentius "last number" method as quoted above:
Example, L Income (the +1 removed from original):
Code: Select all
=index(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today")),len(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today"))),2)
Code: Select all
=index(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today")),len(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today")))-1,2)
-
- Posts: 4
- Joined: Sun Mar 02, 2014 10:15 am
Re: TSP share price in Google Sheets like GoogleFinance
Unfortunately when I past it into the cell, I get the following error.
Function INDEX parameter 2 value is 5. Valid values are between 0 and 4 inclusive.
Any help would be appreciated.
Thank you.
-
- Posts: 49
- Joined: Sat Aug 03, 2019 2:44 pm
Re: TSP share price in Google Sheets like GoogleFinance
@CaesarVincentius,
Thanks.
-
- Posts: 49
- Joined: Sat Aug 03, 2019 2:44 pm
Re: TSP share price in Google Sheets like GoogleFinance
While I am at it, this dirty fix should work-just_learning wrote: ↑Sun Jan 03, 2021 7:33 pm From the looks of it, "+1" will break on 3 day weekends, or more generally when the market is closed for 3 or more consecutive days over the last 5 days... It should fix itself once the market reopens... it is fixable with the use of a little more logic, which I hope someone will take up
@CaesarVincentius,
Thanks.
Code: Select all
=index(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today")),switch(len(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today"))),5,5,4,4,3,3,2,2),14)
- TimeRunner
- Posts: 1939
- Joined: Sat Dec 29, 2012 8:23 pm
- Location: Beach-side, CA
Re: TSP share price in Google Sheets like GoogleFinance
Hah, nicely done there, just_learning! switch( )just_learning wrote: ↑Sun Jan 03, 2021 7:44 pmWhile I am at it, this dirty fix should work-just_learning wrote: ↑Sun Jan 03, 2021 7:33 pm From the looks of it, "+1" will break on 3 day weekends, or more generally when the market is closed for 3 or more consecutive days over the last 5 days... It should fix itself once the market reopens... it is fixable with the use of a little more logic, which I hope someone will take up
@CaesarVincentius,
Thanks.Code: Select all
=index(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today")),switch(len(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today"))),5,5,4,4,3,3,2,2),14)
Re: TSP share price in Google Sheets like GoogleFinance
Thanks! Hopefully this will last more than a day...just_learning wrote: ↑Sun Jan 03, 2021 7:44 pmWhile I am at it, this dirty fix should work-just_learning wrote: ↑Sun Jan 03, 2021 7:33 pm From the looks of it, "+1" will break on 3 day weekends, or more generally when the market is closed for 3 or more consecutive days over the last 5 days... It should fix itself once the market reopens... it is fixable with the use of a little more logic, which I hope someone will take up
@CaesarVincentius,
Thanks.Code: Select all
=index(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today")),switch(len(IMPORTDATA(TEXTJOIN("",TRUE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today"))),5,5,4,4,3,3,2,2),14)
Re: TSP share price in Google Sheets like GoogleFinance
Re: TSP share price in Google Sheets like GoogleFinance
https://www.tsp.gov/contact/
-
- Posts: 7
- Joined: Sat Aug 15, 2020 11:33 am
Re: TSP share price in Google Sheets like GoogleFinance
Good idea. I just added comments as well.Bogtian wrote: ↑Mon Jan 04, 2021 9:38 am I just left some feedback for the web team using the form linked from the "contact us" page - maybe if enough of us check in about it, they'll either give us the old feed back, or a better method for pulling the prices?
https://www.tsp.gov/contact/
- TimeRunner
- Posts: 1939
- Joined: Sat Dec 29, 2012 8:23 pm
- Location: Beach-side, CA
Re: TSP share price in Google Sheets like GoogleFinance
Pick a blank area on your google sheet, say cell K4. Set K4=
Code: Select all
IMPORTDATA(TEXTJOIN("",FALSE,"https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today"))
The formula for today's price (or the price nearest to today in the table) looks up the nearest date to today and then returns the value in the specified column, where the column number in the formula starts with the date column K as column 1. For example, the G Fund is in column V on our sheet, and it's the 12th column in our small table. The formula for today's price (or the price nearest today in the table) for the G Fund is:
Code: Select all
vlookup(today(),$K$5:$Z$9,12)
To find yesterday's price, find the price closest to today, subtract 1 from the actual date found that was closest to today, and then retrieve the G fund price for that day. The formula for yesterday's price (or the price nearest yesterday in the table) for the G Fund is:
Code: Select all
vlookup(vlookup(today(),$K$5:$Z$9,1)-1,$K$5:$Z$9,12)
This will make sense if you give it a try. I make no guarantees, but it seems like it's working so far. We'll see what it looks like this evening!
Re: TSP share price in Google Sheets like GoogleFinance
For fun try these in googlesheets
Code: Select all
=Len(IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate="&TEXT(TODAY()-3,"YYYYMMDD")&"&enddate=today"))
=Len(IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate="&TEXT(TODAY()-4,"YYYYMMDD")&"&enddate=today"))
=Len(IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate="&TEXT(TODAY()-5,"YYYYMMDD")&"&enddate=today"))
=Len(IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate="&TEXT(TODAY()-6,"YYYYMMDD")&"&enddate=today"))
With what is available to us right now, TimeRunner has hit in the solution. Bring in the table on another tab and then reference it on your man tap using a count function to determine which row to grab
Re: TSP share price in Google Sheets like GoogleFinance
First off, the old method in the OP works again. I think it just "reset" for the new year and should work except today you can't get the previous close to calculate the % change. I expect that to work tomorrow.
But to avoid that now and next year there is the below code which leverages the previous work done but solves for the "order of the rows" problem and needing to count to find the most recent date. Pretty slick, google lets you use SQL query commands to manipulate the data in-cell. For once I'll admit, "MS Excel could learn something from Sheets"
Code: Select all
=index(query(IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate="&TEXT(TODAY()-6,"YYYYMMDD")&"&enddate=today"),"select * order by Col1 desc",1),2,12)
- Col: "12" is the fund index you want, in this case G-Fund; use "1" to get the date
- I'm using "TODAY()-6" to ensure I get at least two rows of actual data even with the most expected days between open market dates.
- I also used the "&" text concatenate feature rather than TEXTJOIN; personal preference.
- TimeRunner
- Posts: 1939
- Joined: Sat Dec 29, 2012 8:23 pm
- Location: Beach-side, CA
Re: TSP share price in Google Sheets like GoogleFinance
Thanks for finding that. I was really puzzled at what the LEN function was looking at!
I'm glad there's working solutions from Bogleheads folks to what was a vexing issue. When you have a series of worksheets that suddenly display "#REF" scattered everywhere, it's pretty disconcerting!
Re: TSP share price in Google Sheets like GoogleFinance
So for fun, if you ever find yourself wanting to actually count the number of rows, because changing the order like I did above won't solve your problem, you could have usedTimeRunner wrote: ↑Mon Jan 04, 2021 9:06 pmThanks for finding that. I was really puzzled at what the LEN function was looking at!
I'm glad there's working solutions from Bogleheads folks to what was a vexing issue. When you have a series of worksheets that suddenly display "#REF" scattered everywhere, it's pretty disconcerting!
Code: Select all
=query(IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate="&TEXT(TODAY()-6,"YYYYMMDD")&"&enddate=today"),"select count(Col1)",1)
You might find these three links useful:
https://infoinspired.com/google-docs/sp ... -function/
https://support.google.com/docs/answer/3093343?hl=en
https://developers.google.com/chart/int ... rylanguage
Re: TSP share price in Google Sheets like GoogleFinance
Re: TSP share price in Google Sheets like GoogleFinance
Thanks!mouth wrote: ↑Mon Jan 04, 2021 8:04 pm Okay, I've edited this post like 3 time or more.
First off, the old method in the OP works again. I think it just "reset" for the new year and should work except today you can't get the previous close to calculate the % change. I expect that to work tomorrow.
But to avoid that now and next year there is the below code which leverages the previous work done but solves for the "order of the rows" problem and needing to count to find the most recent date. Pretty slick, google lets you use SQL query commands to manipulate the data in-cell. For once I'll admit, "MS Excel could learn something from Sheets"
- Row: "2" now references the most recent price; use "3" to get the 2nd most recent price, use "1" to get the fund name instead of hard coding itCode: Select all
=index(query(IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate="&TEXT(TODAY()-6,"YYYYMMDD")&"&enddate=today"),"select * order by Col1 desc",1),2,12)
- Col: "12" is the fund index you want, in this case G-Fund; use "1" to get the date
- I'm using "TODAY()-6" to ensure I get at least two rows of actual data even with the most expected days between open market dates.
- I also used the "&" text concatenate feature rather than TEXTJOIN; personal preference.
Re: TSP share price in Google Sheets like GoogleFinance
This provides all the raw data (for all the L funds) and saves to a file "tsp.csv" in your home directory:
Code: Select all
wget -O ~/tsp.csv "https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=%22,TEXT(TODAY()-5,%22YYYYMMDD%22),%22&enddate=today%22))"
Code: Select all
sed -e :a -e '$q;N;61,$D;ba' ~/tsp.csv > ~/tsp60.csv
Re: TSP share price in Google Sheets like GoogleFinance
Re: TSP share price in Google Sheets like GoogleFinance
Date (Don't forget to format the Google sheet cell as a date)
Code: Select all
=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,1)
Code: Select all
=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,2)
Code: Select all
=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,3)
Code: Select all
=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,4)
Code: Select all
=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,5)
Code: Select all
=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,6)
Here is a notional example of how the TSP data can be used in a Google spreadsheet. The cells highlighted in yellow use the above formulas.
Hope this is helpful.
Re: TSP share price in Google Sheets like GoogleFinance
I can see the current G fund rate at the 2) url above. How can I have the result of that page be displayed in a cell of my Google Sheet?mouth wrote: ↑Sat Jan 02, 2021 8:18 am My apologies for not taking the time to write the code but for anyone interested here are two useful facts if you care to keep easy track.
1) The TSP loan interest rate is the G-Fund rate (https://www.tsp.gov/loan-basics/loan-types-and-terms/)
2) The URL to pull that value is https://secure.tsp.gov/components/CORS/ ... tRate.html
...
Thanks.
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: TSP share price in Google Sheets like GoogleFinance
I'm trying, but I can't get it to work. I'm trying Google Sheet's ImportXml function but the results always strip out the number and I just get "--%"Groundhog wrote: ↑Mon Mar 01, 2021 10:09 pmI can see the current G fund rate at the 2) url above. How can I have the result of that page be displayed in a cell of my Google Sheet?mouth wrote: ↑Sat Jan 02, 2021 8:18 am My apologies for not taking the time to write the code but for anyone interested here are two useful facts if you care to keep easy track.
1) The TSP loan interest rate is the G-Fund rate (https://www.tsp.gov/loan-basics/loan-types-and-terms/)
2) The URL to pull that value is https://secure.tsp.gov/components/CORS/ ... tRate.html
...
Thanks.
In theory, any of these below should work, but none of them do.
Code: Select all
=importxml("https://www.tsp.gov/news-and-resources/", "//div[@id='loan-rate']")
Code: Select all
=importxml("https://www.tsp.gov/news-and-resources/", "//*[@id='loan-rate']")
Code: Select all
=ImportXml("https://www.tsp.gov/loan-basics/", "//span[@id='loan-rate']")
Code: Select all
=ImportXml("https://www.tsp.gov/loan-basics/", "//*[@id='loan-rate']")
Code: Select all
=importxml("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html", "/html/body/pre")
Re: TSP share price in Google Sheets like GoogleFinance
this is working on my google sheets pagehoppy08520 wrote: ↑Tue Mar 02, 2021 11:36 amI'm trying, but I can't get it to work. I'm trying Google Sheet's ImportXml function but the results always strip out the number and I just get "--%"Groundhog wrote: ↑Mon Mar 01, 2021 10:09 pmI can see the current G fund rate at the 2) url above. How can I have the result of that page be displayed in a cell of my Google Sheet?mouth wrote: ↑Sat Jan 02, 2021 8:18 am My apologies for not taking the time to write the code but for anyone interested here are two useful facts if you care to keep easy track.
1) The TSP loan interest rate is the G-Fund rate (https://www.tsp.gov/loan-basics/loan-types-and-terms/)
2) The URL to pull that value is https://secure.tsp.gov/components/CORS/ ... tRate.html
...
Thanks.
In theory, any of these below should work, but none of them do.
Code: Select all
=importxml("https://www.tsp.gov/news-and-resources/", "//div[@id='loan-rate']")
Code: Select all
=importxml("https://www.tsp.gov/news-and-resources/", "//*[@id='loan-rate']")
Code: Select all
=ImportXml("https://www.tsp.gov/loan-basics/", "//span[@id='loan-rate']")
The URL you suggested gives me an error for this formula:Code: Select all
=ImportXml("https://www.tsp.gov/loan-basics/", "//*[@id='loan-rate']")
Makes me wonder if the TSP has something on their page that defeats scrapers.Code: Select all
=importxml("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html", "/html/body/pre")
Code: Select all
=importdata("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html")
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: TSP share price in Google Sheets like GoogleFinance
Cool, I hadn't heard of the importdata() function before! I'll have to remember that.mouth wrote: ↑Wed Mar 03, 2021 7:19 am this is working on my google sheets page
Code: Select all
=importdata("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html")
Re: TSP share price in Google Sheets like GoogleFinance
Great, thanks!mouth wrote: ↑Wed Mar 03, 2021 7:19 amthis is working on my google sheets pagehoppy08520 wrote: ↑Tue Mar 02, 2021 11:36 amI'm trying, but I can't get it to work. I'm trying Google Sheet's ImportXml function but the results always strip out the number and I just get "--%"Groundhog wrote: ↑Mon Mar 01, 2021 10:09 pmI can see the current G fund rate at the 2) url above. How can I have the result of that page be displayed in a cell of my Google Sheet?mouth wrote: ↑Sat Jan 02, 2021 8:18 am My apologies for not taking the time to write the code but for anyone interested here are two useful facts if you care to keep easy track.
1) The TSP loan interest rate is the G-Fund rate (https://www.tsp.gov/loan-basics/loan-types-and-terms/)
2) The URL to pull that value is https://secure.tsp.gov/components/CORS/ ... tRate.html
...
Thanks.
In theory, any of these below should work, but none of them do.
Code: Select all
=importxml("https://www.tsp.gov/news-and-resources/", "//div[@id='loan-rate']")
Code: Select all
=importxml("https://www.tsp.gov/news-and-resources/", "//*[@id='loan-rate']")
Code: Select all
=ImportXml("https://www.tsp.gov/loan-basics/", "//span[@id='loan-rate']")
The URL you suggested gives me an error for this formula:Code: Select all
=ImportXml("https://www.tsp.gov/loan-basics/", "//*[@id='loan-rate']")
Makes me wonder if the TSP has something on their page that defeats scrapers.Code: Select all
=importxml("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html", "/html/body/pre")
Code: Select all
=importdata("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html")
Re: TSP share price in Google Sheets like GoogleFinance
google's documentation is actually pretty good for this stuff. I had never played with it at all until this all started breaking. So I googled "importxml sheets" and landed here https://support.google.com/docs/answer/3093342?hl=en and as you can see on the right side there's other functions like [drum roll] importdata.hoppy08520 wrote: ↑Wed Mar 03, 2021 9:28 amCool, I hadn't heard of the importdata() function before! I'll have to remember that.mouth wrote: ↑Wed Mar 03, 2021 7:19 am this is working on my google sheets page
Code: Select all
=importdata("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html")
down the black-hole I fell hahah
glad I could contribute!
Re: TSP share price in Google Sheets like GoogleFinance
Re: TSP share price in Google Sheets like GoogleFinance
Code: Select all
=Index(ImportHtml("http://www.tspfolio.com/tspfunds", "table", 1), 2, 2)
Re: TSP share price in Google Sheets like GoogleFinance
Yup. And seemed to have broken for pulling other prices from morning star as well. What's infuriating is that the error is claiming it can't fetch the data, meanwhile the actual URL works if pasted into a new tab and returns in the formats expected. Feels like Google is being blocked from these sites. I'd say google is being annoying trying to nudge people to use their formula but it isn't like they can't access our data already; but maybe they see this as an easier and lower bandwidth way to do it [shrug]
For non-TSP assets I migrated back over to the GOOGLEFINANCE() function. I can't recall why I switched away. My guess is the daily NAV update was too delayed but I can't validate that on a Saturday. I know I know, not very bogleheady to want daily updates on my accounts. All I can say is I look often in up markets and actually tend to stop looking in down markets, so end result is I don't end up making emotional decisions ... so far
For TSP I guess I'll go back to the TSPFOLIO method even though I don't love it.
Thanks eob616
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: TSP share price in Google Sheets like GoogleFinance
My backup formula for the G Fund:
Code: Select all
=INDEX(importhtml("http://www.tsptalk.com/tsp_share_prices.php", "table", 0), 3, 2)
I'm looking at the affected TSP pages, and I can't figure out what's wrong. As others have written, you can open those URLs in a browser and they do work fine. I'm guessing that this is a short term glitch on Google's end, but I'm not sure. If I can remember, I'll try it again in a week or so.
- TimeRunner
- Posts: 1939
- Joined: Sat Dec 29, 2012 8:23 pm
- Location: Beach-side, CA
Re: TSP share price in Google Sheets like GoogleFinance
Code: Select all
https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today"
Re: TSP share price in Google Sheets like GoogleFinance
hoppy08520 wrote: ↑Sat Nov 20, 2021 8:21 am The TSP sites aren't working for me either. As a backup, my spreadsheet uses tsptalk.com. I'd prefer to use the actual TSP sites, but...
My backup formula for the G Fund:For the other TSP funds, change the last number to 3, 4, 5, 6 for F, C, S, I, respectively.Code: Select all
=INDEX(importhtml("http://www.tsptalk.com/tsp_share_prices.php", "table", 0), 3, 2)
I'm looking at the affected TSP pages, and I can't figure out what's wrong. As others have written, you can open those URLs in a browser and they do work fine. I'm guessing that this is a short term glitch on Google's end, but I'm not sure. If I can remember, I'll try it again in a week or so.
Thanks for posting this! Just wanted to add that if you want to pull more than one day of price info at a time, you can use this formula to pull from TSP talk as well:
Code: Select all
=INDEX(importhtml("https://www.tsptalk.com/tracker/tsp_fund_price.php", "table", 10), 1, 12)
1 = Date
2 = G fund
3 = F Fund
4 = C Fund
5 = S Fund
6 = I Fund
7 = L2065 Fund
8 = L2060 Fund
......etc.
Re: TSP share price in Google Sheets like GoogleFinance
Both methods below are working as of this evening:
Method 1:
Code: Select all
=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?L2040=1", "table", 1), 2,2)
Code: Select all
=index(query(IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate="&TEXT(TODAY()-32,"YYYYMMDD")&"&enddate=today"),"select * order by Col1 desc",1),2,6)
-
- Posts: 7
- Joined: Sat Aug 15, 2020 11:33 am
Re: TSP share price in Google Sheets like GoogleFinance
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: TSP share price in Google Sheets like GoogleFinance
I prefer to use TSP.gov's share prices, but if that service is down, then my Google Sheet falls back to use TSP Talk as a backup, and if that's down, then TSP Folio. With this redundancy, my spreadsheet is almost always working because at least one of those 3 services is usually up.Futbolista wrote: ↑Fri Dec 03, 2021 6:39 pm Are there any downsides to using the TSPtalk links? I was using them for a week or so then decided to switch back to TSP.gov last night, but of course the TSP.gov links quit working this afternoon.
I would say that the "downside" of using the other services is that they aren't the official US Government. I am assuming the other two services retrieve their data from the US Government and cache the data on a continual basis. That's good enough for me.
My sheet's order of precedence:
- https://secure.tsp.gov/components/CORS/ ... esRaw.html
- http://www.tsptalk.com/tsp_share_prices.php
- http://www.tspfolio.com/tspfunds
Re: TSP share price in Google Sheets like GoogleFinance
Code: Select all
=IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?L2050=1&InvFunds=1")
https://secure.tsp.gov/components/CORS/ ... e=20211206
(InvFunds shows the five core funds, G, F, C, S, and I.)
I can usually also query start and end dates by adding
Code: Select all
&startdate=yyyymmdd&enddate=yyyymmdd
Code: Select all
=importdata(concatenate("http://secure.tsp.gov/components/CORS/getSharePricesRaw.html?L2040=1&L2050=1&InvFunds=1&startdate=",text(B1,"YYYYMMDD"),"&enddate=",text(C1,"YYYYMMDD")))
I wonder if there's a way to use the table at https://www.tsptalk.com/tracker/tsp_fund_price.php and add query language for start- and end- dates. Or figure out how it gets the data from tsp.gov and go that route instead.
-
- Posts: 49
- Joined: Sat Aug 03, 2019 2:44 pm
Re: TSP share price in Google Sheets like GoogleFinance
ImportHtml("https://secure.tsp.gov/components/CORS/ ... s.html?C=1", "table", 1) used to work and it seems to have stopped...
I see no data coming back...
Happy new year!
- hoppy08520
- Posts: 2193
- Joined: Sat Feb 18, 2012 10:36 am
Re: TSP share price in Google Sheets like GoogleFinance
Yes, this URL is not working for me right now either:just_learning wrote: ↑Mon Jan 03, 2022 7:49 am Is this broken now?
ImportHtml("https://secure.tsp.gov/components/CORS/ ... s.html?C=1", "table", 1) used to work and it seems to have stopped...
I see no data coming back...
Happy new year!
Code: Select all
https://secure.tsp.gov/components/CORS/getSharePrices.html
This seems to happen frequently which is why my own Sheet uses some backup services from other websites. See more at: viewtopic.php?p=6367928#p6367928
-
- Posts: 49
- Joined: Sat Aug 03, 2019 2:44 pm
Re: TSP share price in Google Sheets like GoogleFinance
I switched to TSPFolio.com
Re: TSP share price in Google Sheets like GoogleFinance
- slowandsteadywins
- Posts: 301
- Joined: Tue Dec 20, 2016 2:13 pm
Re: TSP share price in Google Sheets like GoogleFinance
Re: TSP share price in Google Sheets like GoogleFinance
With the TSP transition to the new system now completed, all functions to the Google sheet are broken and now useless. Is anyone able to figure out how to make it work again.
I have tried changing the URL to the new site (https://www.tsp.gov/fund-performance/sh ... ce-history), but I can't get it to work. I don't know whether the list where share prices are listed is a "TABLE" or what number that item is. For those of you using the table in TSPTALK.com, can you please post the formula to use the function using that site?
Thank you in advance.
- TimeRunner
- Posts: 1939
- Joined: Sat Dec 29, 2012 8:23 pm
- Location: Beach-side, CA
Re: TSP share price in Google Sheets like GoogleFinance
See here: viewtopic.php?p=6707551#p6707551Hdez wrote: ↑Fri Jun 03, 2022 8:11 pm Hi all,
With the TSP transition to the new system now completed, all functions to the Google sheet are broken and now useless. Is anyone able to figure out how to make it work again.
I have tried changing the URL to the new site (https://www.tsp.gov/fund-performance/sh ... ce-history), but I can't get it to work. I don't know whether the list where share prices are listed is a "TABLE" or what number that item is. For those of you using the table in TSPTALK.com, can you please post the formula to use the function using that site?
Thank you in advance.
Re: TSP share price in Google Sheets like GoogleFinance
Here is a URL that used to work, but now returns "Sorry, we could not find that page"
Code: Select all
https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=%22,TEXT(TODAY()-5,%22YYYYMMDD%22),%22&enddate=today%22))