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: 1562
Joined: Wed Mar 10, 2010 10:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

CaesarVincentius wrote: Fri Jan 01, 2021 3: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: 243
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: 725
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: 1562
Joined: Wed Mar 10, 2010 10:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

Gadget wrote: Sun Jan 03, 2021 9:45 am The TSP people don't want us to have nice things. Seems they broke it again.
I just noticed that too 😞
User avatar
wander
Posts: 3686
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: 737
Joined: Fri Apr 01, 2011 11:07 pm
Location: Just west of Austin TX

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: 1660
Joined: Sat Dec 29, 2012 9:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

CaesarVincentius wrote: Fri Jan 01, 2021 3: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.
newtosaving
Posts: 4
Joined: Sun Mar 02, 2014 11: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: 18
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: 18
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 8: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: 1660
Joined: Sat Dec 29, 2012 9: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 8:44 pm
just_learning wrote: Sun Jan 03, 2021 8: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.
Cash
Posts: 1562
Joined: Wed Mar 10, 2010 10:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

just_learning wrote: Sun Jan 03, 2021 8:44 pm
just_learning wrote: Sun Jan 03, 2021 8: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: 725
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: 3
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: 6
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 10: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: 1660
Joined: Sat Dec 29, 2012 9: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.
mouth
Posts: 243
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: 243
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 10:21 pm, edited 1 time in total.
User avatar
TimeRunner
Posts: 1660
Joined: Sat Dec 29, 2012 9:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

mouth wrote: Mon Jan 04, 2021 7: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.
mouth
Posts: 243
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 10:06 pm
mouth wrote: Mon Jan 04, 2021 7: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 2:29 pm
Cash wrote: Fri Jan 01, 2021 1: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: 1562
Joined: Wed Mar 10, 2010 10:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

mouth wrote: Mon Jan 04, 2021 9: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: 737
Joined: Fri Apr 01, 2011 11:07 pm
Location: Just west of Austin TX

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: 682
Joined: Sun Nov 25, 2012 6: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: 1
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: 223
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 9: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: 2174
Joined: Sat Feb 18, 2012 11:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

Groundhog wrote: Mon Mar 01, 2021 11:09 pm
mouth wrote: Sat Jan 02, 2021 9: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: 243
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 12:36 pm
Groundhog wrote: Mon Mar 01, 2021 11:09 pm
mouth wrote: Sat Jan 02, 2021 9: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: 2174
Joined: Sat Feb 18, 2012 11:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

mouth wrote: Wed Mar 03, 2021 8: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: 223
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 8:19 am
hoppy08520 wrote: Tue Mar 02, 2021 12:36 pm
Groundhog wrote: Mon Mar 01, 2021 11:09 pm
mouth wrote: Sat Jan 02, 2021 9: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: 243
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 10:28 am
mouth wrote: Wed Mar 03, 2021 8: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!
Post Reply