TSP share price in Google Sheets like GoogleFinance

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Cash
Posts: 1572
Joined: Wed Mar 10, 2010 9:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

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.
Excellent, thank you!
mouth
Posts: 654
Joined: Sun Apr 19, 2015 6:40 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by mouth »

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

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.
Gadget
Posts: 1026
Joined: Fri Mar 17, 2017 1:38 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by Gadget »

The TSP people don't want us to have nice things. Seems they broke it again.
Cash
Posts: 1572
Joined: Wed Mar 10, 2010 9:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

Gadget wrote: Sun Jan 03, 2021 8:45 am The TSP people don't want us to have nice things. Seems they broke it again.
I just noticed that too 😞
wander
Posts: 4424
Joined: Sat Oct 04, 2008 9:10 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by wander »

This happens quite often when new employees or bosses think something should be changed. If it doesn't break, people still want to fix.
User avatar
NoRoboGuy
Posts: 882
Joined: Fri Apr 01, 2011 11:07 pm
Location: Alabama
Contact:

Re: TSP share price in Google Sheets like GoogleFinance

Post by NoRoboGuy »

I don't use google, I use Zoho, and it does not work with the revision posted. What used to work was (for L Income and L2025):

Code: Select all

https://secure.tsp.gov/components/CORS/getSharePrices.html?Linc=1

https://secure.tsp.gov/components/CORS/getSharePrices.html?L2025=1
If you go to the below directory, it looks like they disabled the feed that used to be posted to the CORS folder:

Code: Select all

https://secure.tsp.gov/components/CORS
There is no free lunch.
User avatar
TimeRunner
Posts: 1939
Joined: Sat Dec 29, 2012 8:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

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.

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)
The above should work with the last number being the fund you want:
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
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:
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)
Example L Income for previous day (-1 inserted into formula):

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)
We'll see if this works after tomorrow's price update! Again, big kudos to CaesarVincentius. :beer
One cannot enlighten the unconscious. | "All I need are some tasty waves, a cool buzz, and I'm fine." -Jeff Spicoli
newtosaving
Posts: 4
Joined: Sun Mar 02, 2014 10:15 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by newtosaving »

Thank you working this out.

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.
just_learning
Posts: 49
Joined: Sat Aug 03, 2019 2:44 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by just_learning »

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 :twisted:

@CaesarVincentius,
Thanks.
just_learning
Posts: 49
Joined: Sat Aug 03, 2019 2:44 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by just_learning »

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 :twisted:

@CaesarVincentius,
Thanks.
While I am at it, this dirty fix should work-

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)
User avatar
TimeRunner
Posts: 1939
Joined: Sat Dec 29, 2012 8:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

just_learning wrote: Sun Jan 03, 2021 7:44 pm
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 :twisted:

@CaesarVincentius,
Thanks.
While I am at it, this dirty fix should work-

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)
Hah, nicely done there, just_learning! switch( :beer )
One cannot enlighten the unconscious. | "All I need are some tasty waves, a cool buzz, and I'm fine." -Jeff Spicoli
Cash
Posts: 1572
Joined: Wed Mar 10, 2010 9:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

just_learning wrote: Sun Jan 03, 2021 7:44 pm
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 :twisted:

@CaesarVincentius,
Thanks.
While I am at it, this dirty fix should work-

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)
Thanks! Hopefully this will last more than a day...
Gadget
Posts: 1026
Joined: Fri Mar 17, 2017 1:38 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by Gadget »

I didn't get a chance to try this yesterday, but the last one posted doesn't work today.
Bogtian
Posts: 5
Joined: Fri May 03, 2013 9:41 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Bogtian »

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/
Futbolista
Posts: 7
Joined: Sat Aug 15, 2020 11:33 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Futbolista »

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/
Good idea. I just added comments as well.
User avatar
TimeRunner
Posts: 1939
Joined: Sat Dec 29, 2012 8:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

I don't know if this is a great solution, but it's what I'm using right now. Instead of getting lost in formula parentheses in trying to squeeze the solution into one formula, I broke it into edible bites in the hopes of being able to understand and fix it if it stops working.
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"))
Now you should see a small table with row 4 containing the headers "Date L Income L2025..." and so on from column K through the last column Z. Rows 5-9 contain the up to the last 5 days of data as imported from TSP. Note that column K contains the five or less most recent price posting dates as the number of days from 1/1/1900.
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) 
This will return the price of the G Fund for the date closest to today().
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)
I'm using absolute addressing (e.g. the $ in the address) because it makes it easy to copy a series and just change the last number to be the price column you want, eg. 12 becomes 2 for L Income, etc.
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!
One cannot enlighten the unconscious. | "All I need are some tasty waves, a cool buzz, and I'm fine." -Jeff Spicoli
mouth
Posts: 654
Joined: Sun Apr 19, 2015 6:40 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by mouth »

So a heads up ... the LEN function being used is actually just counting the number of letters in the source cell. Aka "Date".

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"))
All resolve to 4. That's why TODAY()-5 was working until it didn't produce 5 results

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
mouth
Posts: 654
Joined: Sun Apr 19, 2015 6:40 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by mouth »

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"

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)
- 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 it
- 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.
Last edited by mouth on Mon Jan 04, 2021 9:21 pm, edited 1 time in total.
User avatar
TimeRunner
Posts: 1939
Joined: Sat Dec 29, 2012 8:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

mouth wrote: Mon Jan 04, 2021 6:50 pm So a heads up ... the LEN function being used is actually just counting the number of letters in the source cell. Aka "Date".
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!
One cannot enlighten the unconscious. | "All I need are some tasty waves, a cool buzz, and I'm fine." -Jeff Spicoli
mouth
Posts: 654
Joined: Sun Apr 19, 2015 6:40 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by mouth »

TimeRunner wrote: Mon Jan 04, 2021 9:06 pm
mouth wrote: Mon Jan 04, 2021 6:50 pm So a heads up ... the LEN function being used is actually just counting the number of letters in the source cell. Aka "Date".
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!
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 used

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)
That'll report 4 when using TODAY()-6 but if you made it TODAY()-20 you'd get 13 ... just to prove it actually changes ;-)

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
User avatar
Hodor
Posts: 240
Joined: Wed Jun 24, 2015 8:27 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by Hodor »

Hodor wrote: Fri Jan 01, 2021 1:29 pm
Cash wrote: Fri Jan 01, 2021 12:25 pm Did they change the location again? I'm getting an error now...
Me too, but I'm wondering if it's because it's now 2021 but there haven't been any trading days yet to fill in the table. If so, it should resolve itself by Tuesday.
It looks like this was the case. My old functions are working fine again.
Cash
Posts: 1572
Joined: Wed Mar 10, 2010 9:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

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"

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)
- 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 it
- 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.
Thanks!
User avatar
NoRoboGuy
Posts: 882
Joined: Fri Apr 01, 2011 11:07 pm
Location: Alabama
Contact:

Re: TSP share price in Google Sheets like GoogleFinance

Post by NoRoboGuy »

For those using Linux and/or do not wish to support Google, the following works in bash:

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))"
This saves only the most recent 60 days from the above file (without column headers) and saves to a file called "tsp60.csv" (note "61" in code):

Code: Select all

sed -e :a -e '$q;N;61,$D;ba' ~/tsp.csv > ~/tsp60.csv
I use this to post to a Zoho spreadsheet. Now it displays a custom chart with just the last 60 days of performance.
There is no free lunch.
User avatar
hornet96
Posts: 811
Joined: Sun Nov 25, 2012 5:45 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by hornet96 »

I just wanted to thank all of you for putting these formulas together and sharing your techncal knowledge and expertise with us on this forum. I use a form of these scripts in Google Sheets to download a CSV file for importing prices into Quicken, and had noticed that the TSP script wasn't working as of yesterday. Sure enough, a Boglehead had already come up with a workable solution. :sharebeer
tyhughes
Posts: 2
Joined: Wed Aug 07, 2013 6:30 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by tyhughes »

There are probably better ways to import TSP prices into a Google spreadsheet, but here is what has worked for me after some trial and error.

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)
G Fund

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,2)
F Fund

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,3)
C Fund

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,4)
S Fund

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,5)
I Fund

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?startdate=20191130&InvFunds=1", "table", 1), 2,6)
You can change the start date in the table used to import the price data. The end date is automatically filled in with the most recent date for which there is data. Current prices are pulled from row 2. The immediately previous day is row 3, etc.

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.

Image

Hope this is helpful.
User avatar
Groundhog
Posts: 264
Joined: Mon Oct 24, 2011 7:10 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by Groundhog »

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
...
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?

Thanks.
User avatar
Topic Author
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

Groundhog wrote: Mon Mar 01, 2021 10:09 pm
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
...
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?

Thanks.
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 "--%"

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']")
The URL you suggested gives me an error for this formula:

Code: Select all

=importxml("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html", "/html/body/pre")
Makes me wonder if the TSP has something on their page that defeats scrapers.
mouth
Posts: 654
Joined: Sun Apr 19, 2015 6:40 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by mouth »

hoppy08520 wrote: Tue Mar 02, 2021 11:36 am
Groundhog wrote: Mon Mar 01, 2021 10:09 pm
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
...
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?

Thanks.
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 "--%"

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']")
The URL you suggested gives me an error for this formula:

Code: Select all

=importxml("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html", "/html/body/pre")
Makes me wonder if the TSP has something on their page that defeats scrapers.
this is working on my google sheets page

Code: Select all

=importdata("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html")
User avatar
Topic Author
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

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")
Cool, I hadn't heard of the importdata() function before! I'll have to remember that.
User avatar
Groundhog
Posts: 264
Joined: Mon Oct 24, 2011 7:10 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by Groundhog »

mouth wrote: Wed Mar 03, 2021 7:19 am
hoppy08520 wrote: Tue Mar 02, 2021 11:36 am
Groundhog wrote: Mon Mar 01, 2021 10:09 pm
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
...
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?

Thanks.
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 "--%"

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']")
The URL you suggested gives me an error for this formula:

Code: Select all

=importxml("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html", "/html/body/pre")
Makes me wonder if the TSP has something on their page that defeats scrapers.
this is working on my google sheets page

Code: Select all

=importdata("https://secure.tsp.gov/components/CORS/getCurrentLoanInterestRate.html")
Great, thanks!
mouth
Posts: 654
Joined: Sun Apr 19, 2015 6:40 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by mouth »

hoppy08520 wrote: Wed Mar 03, 2021 9:28 am
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")
Cool, I hadn't heard of the importdata() function before! I'll have to remember that.
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.

down the black-hole I fell hahah

glad I could contribute!
User avatar
skow
Posts: 137
Joined: Thu Mar 08, 2007 11:23 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by skow »

Did anyone else's formulas for pulling share prices break in the last day or two? I can't figure out what went wrong.
rama13
Posts: 72
Joined: Sun May 15, 2011 2:09 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by rama13 »

Yup!!
eob616
Posts: 52
Joined: Sun Aug 26, 2018 10:01 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by eob616 »

This still works:

Code: Select all

=Index(ImportHtml("http://www.tspfolio.com/tspfunds", "table", 1), 2, 2)
(Example is for G fund)
mouth
Posts: 654
Joined: Sun Apr 19, 2015 6:40 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by mouth »

skow wrote: Wed Nov 17, 2021 10:49 pm Did anyone else's formulas for pulling share prices break in the last day or two? I can't figure out what went wrong.
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
User avatar
Topic Author
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

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:

Code: Select all

=INDEX(importhtml("http://www.tsptalk.com/tsp_share_prices.php", "table", 0), 3, 2)
For the other TSP funds, change the last number to 3, 4, 5, 6 for F, C, S, I, respectively.

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.
User avatar
TimeRunner
Posts: 1939
Joined: Sat Dec 29, 2012 8:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

When I put this into the browser:

Code: Select all

https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=",TEXT(TODAY()-5,"YYYYMMDD"),"&enddate=today"
then instead of getting the last five days, it retrieves the entire share price history starting with June 2, 2003, and it's not working inside of google sheets. I understand from prior posts it's not working in Google sheets, but I don't understand why it's pulling so much info. :confused
One cannot enlighten the unconscious. | "All I need are some tasty waves, a cool buzz, and I'm fine." -Jeff Spicoli
User avatar
hornet96
Posts: 811
Joined: Sun Nov 25, 2012 5:45 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by hornet96 »

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:

Code: Select all

=INDEX(importhtml("http://www.tsptalk.com/tsp_share_prices.php", "table", 0), 3, 2)
For the other TSP funds, change the last number to 3, 4, 5, 6 for F, C, S, I, respectively.

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)
Where the last "12" represents the L2040 fund.

1 = Date
2 = G fund
3 = F Fund
4 = C Fund
5 = S Fund
6 = I Fund
7 = L2065 Fund
8 = L2060 Fund
......etc.
User avatar
hornet96
Posts: 811
Joined: Sun Nov 25, 2012 5:45 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by hornet96 »

Also just as a data point, the "usual" formulas weren't working this morning; however, when I went to check just now (after 5PM Eastern) they both were pulling in data from the TSP site. Not sure if it's a TSP or a Google issue, but to me the problems seem to relate to the way that TSP is updating/organizing their data.

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)
Method 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)
Futbolista
Posts: 7
Joined: Sat Aug 15, 2020 11:33 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Futbolista »

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.
User avatar
Topic Author
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

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

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:
  1. https://secure.tsp.gov/components/CORS/ ... esRaw.html
  2. http://www.tsptalk.com/tsp_share_prices.php
  3. http://www.tspfolio.com/tspfunds
I have a Google Sheet that shows a fairly simple and efficient way to use all 3 services, so that if any one of them is down, it uses the others as a backup. I did this because I got tired of switching back and forth between different services whenever one of them was down temporarily. To improve performance and reduce overhead, it only calls the "backup" services if the preferred service(s) are down. See: TSP Thrift Savings Plan Google Sheets Tips and refer to the 3rd and 4th Sheets on that Spreadsheet.
graceruns
Posts: 1
Joined: Tue Dec 07, 2021 10:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by graceruns »

This usually works but hasn't been working for a week or so.

Code: Select all

=IMPORTDATA("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?L2050=1&InvFunds=1")
I'm getting a "could not fetch url" error in google sheets when accessing the tsp.gov raw share prices page. But I can access it outside of google sheets, by just typing the URL and query text into the address bar.
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
to the end after selecting which funds I want to get this:

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")))
In cell B1, I have either a custom date or "=today()-14" for the start date and "=today()" in cell C1.

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.
just_learning
Posts: 49
Joined: Sat Aug 03, 2019 2:44 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by just_learning »

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!
User avatar
Topic Author
hoppy08520
Posts: 2193
Joined: Sat Feb 18, 2012 10:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

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!
Yes, this URL is not working for me right now either:

Code: Select all

https://secure.tsp.gov/components/CORS/getSharePrices.html
I don't know if the problem is with the TSP or Google Sheets.

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
just_learning
Posts: 49
Joined: Sat Aug 03, 2019 2:44 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by just_learning »

Yeah, Thanks.
I switched to TSPFolio.com
Stoic9
Posts: 369
Joined: Mon Dec 10, 2018 6:14 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Stoic9 »

DANGGGGG Cool!
User avatar
slowandsteadywins
Posts: 301
Joined: Tue Dec 20, 2016 2:13 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by slowandsteadywins »

I just found this and it is a game changer! My TSP was the only manual entry, aside from # of shares, in my Google Sheet allocation tracker. Thanks! Also, bumping for other TSPers to discover and celebrate! :sharebeer
"Nothing in this world can take the place of persistence; Persistence and determination alone are omnipotent." | -Calvin Coolidge
Hdez
Posts: 6
Joined: Fri Jun 03, 2022 7:33 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by Hdez »

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.
User avatar
TimeRunner
Posts: 1939
Joined: Sat Dec 29, 2012 8:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

Hdez 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.
See here: viewtopic.php?p=6707551#p6707551
One cannot enlighten the unconscious. | "All I need are some tasty waves, a cool buzz, and I'm fine." -Jeff Spicoli
User avatar
NoRoboGuy
Posts: 882
Joined: Fri Apr 01, 2011 11:07 pm
Location: Alabama
Contact:

Re: TSP share price in Google Sheets like GoogleFinance

Post by NoRoboGuy »

The problem is that the server secure.tsp.gov is still down. I do not know when (or whether) it will be restored. I have code which tests this server before updating TSP prices, and it has still not come back online since the update.

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))
There is no free lunch.
Post Reply