TSP share price in Google Sheets like GoogleFinance

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
Topic Author
hoppy08520
Posts: 2174
Joined: Sat Feb 18, 2012 11:36 am

TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

UPDATE - Jul 12, 2020 - the TSP recently updated the layout of share price webpage to include the new Lifecycle funds with 5-year retirement dates, and that change threw off the formulas that formerly worked. New solution is directly below. Thank you to ma21n2 for the updated solution.

If you track your TSP investments in a Google Sheet and use the GoogleFinance function to get real-time fund share prices, you'll find that GoogleFinance cannot access the US Government's Thrift Savings Plan (TSP) funds, unlike ordinary funds with a recognizable ticker symbol such as "VFINX" for the Vanguard S&P 500 index fund.

Fortunately, there is an alternate way to get up-to-date TSP fund share prices with the following formula. To get the current share price for the G Fund, enter this formula into a cell and it will output the current share price of the fund (e.g. it happens to be $14.9636 on the day I wrote this):

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,2)
For the other TSP funds, change the end of URL above from "G=1" to the following:
  • For the F, C, S, and I funds, change to F=1, C=1, S=1 or I=1.
  • For the Lifecycle Income fund, change to Linc=1
  • For the other Lifecycle funds, change to L2025=1, L2030=1, etc. (Note: as of 7/17/2020, this doesn't seem to be working yet for the newer Lifecycle funds with the mid-decade years but it works on L2030, L2040 and L2050.)
More Details and Background

If you are curious about how this works, or if you want to know of other similar Google sheet tricks and tips with the TSP, or how to do this technique with other "closed" funds that are not on stock exchanges, then read on.

Inspired by a similar thread from The529guy regarding TIAA-CREF funds that GoogleFinance cannot recognize, I wanted to see if there was a way to get real-time quotes for TSP funds in my Google Docs spreadsheet using a similar technique. Lacking this ability, I have been manually updating the funds' prices manually every so often in my spreadsheet. But thanks to the idea from this thread, I no longer need to do this!

For ordinary mutual funds, ETFs, or stocks, you can get the share price with this function:

Code: Select all

=GoogleFinance("VFINX", "price")
Unfortunately, this magical function won't work for TSP Funds or other "closed" funds that aren't on a regular stock exchange.

But if you can find a website that has a real-time (or close) quote, you might be able to hack your way to the same result using the ImportHtml() function that "scrapes" the HTML from the page.

The TSP website has such a webpage: TSP Share Price. If you want to see what it looks like in a Google spreadsheet, just enter this into a cell:

Code: Select all

=ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1)
You'll see how this function outputs the prices for each fund in a grid of rows (corresponding to dates, most recent on top) and columns (one column for each fund). The first row is the header, and the second row contains the share prices for the most recent date. The first column is the date, and the funds begin with the 2nd column.

Using the INDEX() function, you can grab the the value of the cell containing the share price for a specific fund like this below. This will grab the value in the cell that is in the 2rd row and the 2nd column, which happens to be the current price of the G Fund:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,2)
The code above will output the current price of the G Fund directly into the cell.

For the other TSP funds, change the the URL path for each fund, as described in the explanation at the top of this post.

DISCLAIMER: If the layout of the underlying TSP web page changes, then all bets are off. You may need to adapt to the new web page by tweaking the row/column coordinates. If I notice changes, I'll update this post (if I can remember -- if not, PM me).

More tips:

Sample Google Sheet Illustrating various TSP Tips

TSP GoogleFinance Tips - Google Sheet
Make a copy of this Google Sheet if you'd like to see how some of these case studies work.

Fund's Year to Date (YTD) Return
[Note: I updated this section on 5 February 2016. Previously, I was scraping the html from a TSP.gov web page, but I found that page's formatting difficult to work with so I started getting the data from a different web page from TSP Folio instead.]

To get the G Fund's YTD return from TSP Folio's Fund Page page, put this into a cell:

Code: Select all

=Index(ImportHtml("http://www.tspfolio.com/tspfunds", "table", 1), 4, 2)
The value returned from this formula will be a decimal, e.g. if the fund is up 2.5% for the year, then the raw value will be 0.025.

For the other TSP funds, change the values of the first and third numbers in the formula (which are "1" and "2" in the G Fund example shown above, leaving the number "4" the same for all TSP funds) as follows:
  • G Fund - 1 and 2 (as shown in the formula above)
  • F Fund - 1 and 3
  • C Fund - 1 and 4
  • S Fund - 1 and 5
  • I Fund - 1 and 6
  • L Income - 2 and 2
  • L 2020 - 2 and 3
  • L 2030 - 2 and 4
  • L 2040 - 2 and 5
  • L 2050 - 2 and 6
The TSP Folio site is not an official TSP page, but I found it easier to extract the data from their webpage compared to similar TSP pages.

Fund Closing Price on Specific Date

To get a fund's closing price on a particular date, try the following. This will work only if the date is a weekday when the share price is published. Therefore you must ensure that you use a valid date.

Code: Select all

=INDEX(IMPORTHTML("https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html?startdate=" & TEXT("01/03/2011", "mm/dd/yyyy") & "&enddate=" & TEXT("01/03/2011", "mm/dd/yyyy"), "table", 0), 2, 7)
This example uses the date "01/03/2011". Presumably you'll reference the date with a value in another column. The TEXT function is used to format the date in mm/dd/yyyy format, which is the format the web page requires to operate. The last number specifies which fund. Use the same list of numbers as above. The number in this example (7) is for the G Fund.

Note: while it was interesting to figure out how to get a price on a particular date, it might be better to just save all this historical information into your spreadsheet as fixed data, given that it won't change. Also, storing the actual data will make your spreadsheet load faster.

Current G Fund Yield

Scraped from TSP Folio - What is the current TSP G Fund interest rate?:

Code: Select all

=index(ImportHtml("http://www.tspfolio.com/tspgfundinterestrate", "table", 0), 2, 2)
I can't find a way to do this for the other TSP funds, but given that the TSP doesn't pay dividends (it plows the dividends from the stocks back into the fund), the "yield" for other TSP funds isn't really relevant.

Applying this Technique to other Closed Funds

If you somehow stumbled onto this thread and are trying to do something similar for non-TSP funds that are Collective Investment Trusts that are not recognized by the GoogleFinance function because they don't have an ordinary ticker symbol, then you can use this same technique for your own funds, as long as you can find a web page that spits out the funds' current share price, and as long as the web page is formatted in a way that you can use the INDEX function. Ideally the page will output the prices in table/row/cell format. Increasingly, web page design is moving toward DIV-based formatting and away from HTML table/row/columns. If the page does not have table/row/columns, you might be able to use ImportXML instead...it can be bewildering to work with ImportXML if you're not familiar with modern webpage design so I recommend googling for tips.

Updates
I will post any updates below, most recent updates on the top.

UPDATE - Jul 11, 2020

The TSP overhauled the layout of share price web page which caused the earlier solutions to break. A clever Boglehead named ma21n2 discovered an alternate TSP web page from which you can scrape current TSP share prices. That solution is here:
ma21n2 wrote: Sat Jul 11, 2020 1:09 pmLooks like getSharePrice.html returns a table in HTML format. This gets me the latest price of the G fund:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,2)
For F fund, you'd just change "G=1" to "F=1", etc.
I took the liberty of updating my OP to use the solution of ma21n2 so that new readers will see the solution right away and not have to scroll down looking for it.

Let's hope the TSP keeps this webpage running for a while...

UPDATE - Jul 2, 2020

The TSP recently updated the share price webpage to include the new Lifecycle funds with 5-year retirement dates, and that change threw off the column numbers for each fund. The new column numbers are updated in the OP. You'll need to change your spreadsheet to reflect this change. The previous column numbers were 7, 8, 9, 10, 11 for G, F, C, S, I. New column numbers after the update are 12, 13, 14, 15, 16. The Lifecycle fund columns also changed as new ones were introduced, and the L2020 fund has been retired. See updated OP with fix.

UPDATE - February 18, 2019

It appears that in the past couple of days, the TSP has taken down the sharePriceHistory.shtml webpage that once provided fund share prices, and was the page used in the examples above, which will cause your spreadsheets to break. Fortunately, the FundPerformance/index.html page can be used instead. To correct your Google sheets, change from the old URL to the new URL. The other parameters (table, row, column) do not need to be changed, just the URL.

New functioning URL:

Code: Select all

https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html
Old broken URL:

Code: Select all

https://www.tsp.gov/investmentfunds/shareprice/sharePriceHistory.shtml
UPDATE - 7 April 2015 - The TSP changed the formatting on the TSP: Share Price History web page (sharePriceHistory.shtml), which requires you to update your Google sheet and change the "table" parameter in the ImportHtml formula from "0" to "2" as shown in the code snippets above showing how to get the current share price.

Oddly, this is necessary only when you are using this HTML page to get the current share price (which is probably the most common scenario). If you're using the ImportHtml formula to get a closing price on a date in the past, you must continue to use the "0" parameter for the table in the ImportHtml function.
Last edited by hoppy08520 on Fri Jul 17, 2020 8:11 am, edited 21 times in total.
User avatar
LadyGeek
Site Admin
Posts: 72115
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: GoogleFinance price quotes for TSP Funds

Post by LadyGeek »

Consider putting it in the wiki, perhaps as a new "Real-time tracking" section in Using a spreadsheet to maintain a portfolio.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Topic Author
hoppy08520
Posts: 2174
Joined: Sat Feb 18, 2012 11:36 am

Re: GoogleFinance price quotes for TSP Funds

Post by hoppy08520 »

LadyGeek wrote:Consider putting it in the wiki, perhaps as a new "Real-time tracking" section in Using a spreadsheet to maintain a portfolio.
I already did:

http://www.bogleheads.org/wiki/Using_a_ ... l_function

:D
User avatar
LadyGeek
Site Admin
Posts: 72115
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: GoogleFinance price quotes for TSP Funds

Post by LadyGeek »

You're right. :oops: I don't understand the C-fund. Does "collective investment trust" also mean "commingled trust"?

In Using a spreadsheet to maintain a portfolio:
... C Fund, which is a 500-index collective investment trust (CIT)
But in Thrift Savings Plan:
C Fund: a large-to-mid cap stock index commingled trust that tracks the S&P 500 Stock Index.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Cash
Posts: 1562
Joined: Wed Mar 10, 2010 10:52 am

Re: GoogleFinance price quotes for TSP Funds

Post by Cash »

This is great. Thanks, hoppy!
twindad57
Posts: 124
Joined: Fri Jan 03, 2014 3:30 pm

Re: GoogleFinance price quotes for TSP Funds

Post by twindad57 »

Great work, Hoppy!
Dr_McGarvey
Posts: 64
Joined: Tue Feb 23, 2010 7:31 pm

Re: GoogleFinance price quotes for TSP Funds

Post by Dr_McGarvey »

Nice work, Hoppy! Thanks a lot.
chudder
Posts: 27
Joined: Wed May 11, 2011 12:54 am

Re: GoogleFinance price quotes for TSP Funds

Post by chudder »

Awesome thanks for figuring this out for the rest of us!!
gtwhitegold
Posts: 500
Joined: Fri Sep 21, 2012 1:55 pm

Re: GoogleFinance price quotes for TSP Funds

Post by gtwhitegold »

Thanks. I had been using a script I found before, possibly from this forum as well, but I don't really recall. It is a lot simpler this way. I also implemented it for my path2college 529 plan as well. It took a little playing with that one, but it works as well now. For 529 plans, using the core TIAA-CREF fund info will cause tracking error due to the overhead costs of the plan. Now if I could get it to log in to Navy Federal to check my IRA certificate balance, I would be pleased as punch.

Allen
User avatar
The529guy
Posts: 610
Joined: Fri May 23, 2014 1:08 am

Re: GoogleFinance price quotes for TSP Funds

Post by The529guy »

Good idea, gtwhitegold! I just tried it out on the CA 529 plan's daily price page...

Code: Select all

=Index(ImportHtml("https://www.scholarshare.com/invest/dailyprice.shtml", "table", 0), 40, 2)
...where the 40th row, 2nd column, is the unit value for the Index U.S. Equity Investment Portfolio.

Unfortunately, I'm out of luck with the NY 529 plan's website.
gtwhitegold
Posts: 500
Joined: Fri Sep 21, 2012 1:55 pm

Re: GoogleFinance price quotes for TSP Funds

Post by gtwhitegold »

Yeah, The529guy, I guess that you are out of luck. I tried to find different ways to add the information, but the closest I found was where one fund was listed on the bloomberg.com site, but I was unable to find any of the others there. My recommendation would be to just use the fund data from the Vanguard website and adjust as necessary.

Allen
User avatar
Topic Author
hoppy08520
Posts: 2174
Joined: Sat Feb 18, 2012 11:36 am

Re: GoogleFinance price quotes for TSP Funds

Post by hoppy08520 »

FYI, for anyone who is interested I updated the original post with some more Google Sheets tips for TSP fund data, including a link to a sample spreadsheet.

Also:
The529guy wrote:Good idea, gtwhitegold! I just tried it out on the CA 529 plan's daily price page...

Code: Select all

=Index(ImportHtml("https://www.scholarshare.com/invest/dailyprice.shtml", "table", 0), 40, 2)
...where the 40th row, 2nd column, is the unit value for the Index U.S. Equity Investment Portfolio.
Here's an alternate way to do this that I learned in working with a similar page with TSP fund data. When I see that this fund is the 40th row, it makes me nervous that this page could add a new fund (or two) or remove a fund and throw off the row numbering system you're counting on. This could cause your fund to move up or down a row and "40" would no longer be the correct row.

Another way to do this is the following. You can use a QUERY to specify the specific row you want from the Table:

Code: Select all

=Index(Query(ImportHtml("https://www.scholarshare.com/invest/dailyprice.shtml", "table", 0), "select Col2 where Col1='*Index U.S. Equity Investment Portfolio*'"), 2, 1) 
Granted, this approach can be fragile as well, if they change the name of the fund slightly. But this might (or might not?) be more stable than hoping that the fund always stays in the 40th row.

Here's an even better approach that uses the LIKE operator in the QUERY instead of EQUALS (=) to guard against the fund name having the leading and trailing asterisk removed from the fund name, or the periods from U.S. The percent symbol (%) represents a wildcard. I also use the LOWER() function to guard against case changes, because QUERY is case sensitive in Google:

Code: Select all

=Index(Query(ImportHtml("https://www.scholarshare.com/invest/dailyprice.shtml", "table", 0), "select Col2 where lower(Col1) like lower('%Index U%S% Equity Investment Portfolio%')"), 2, 1)
This is probably overkill, but I'm just an occasional programmer geeking out late at night and I can't help myself...I hope this case study may help someone with this or a slightly different application.
User avatar
Topic Author
hoppy08520
Posts: 2174
Joined: Sat Feb 18, 2012 11:36 am

Re: GoogleFinance price quotes for TSP Funds

Post by hoppy08520 »

If anyone is using the TSP web page sharePriceHistory.shtml to "scrape" the HTML to get up-to-date share prices for TSP funds, be aware that the TSP seems to have just changed the formatting of that page around April 7, 2015, which may require you to update some formulas in your code or spreadsheets that gets this data.

If your spreadsheets are getting errors on TSP share prices, this is likely why.

In my case, it was easy to fix -- I just had to change a "0" to a "2"; see below for details. I also updated the original post.

UPDATE - 7 April 2015 - The TSP changed the formatting on the TSP: Share Price History web page (sharePriceHistory.shtml), which requires you to update your Google sheet and change the "table" parameter in the ImportHtml formula from "0" to "2" as shown in the code snippets above (in the original post) showing how to get the current share price.

Oddly, this is necessary only when you are using this HTML page to get the current share price (which is probably the most common scenario). If you're using the ImportHtml formula to get a closing price on a date in the past, you must continue to use the "0" parameter for the table in the ImportHtml function.
xPat
Posts: 13
Joined: Mon Jan 19, 2015 5:37 am

Re: GoogleFinance price quotes for TSP Funds

Post by xPat »

Thank you, hoppy08520, for these tips; they're very useful for my spreadsheets.

Thinking about portfolio allocations and the fact that I mostly use the L 2050 fund with TSP, I decided to make another tool and share it with you all. Since TSP doesn't seem to share their Lifecycle fund allocation quarterly changes in a similar format, I went ahead and scraped all of them and put them into a Google Sheet. Then, to make myself feel more accomplished, I just had to write a few complex Index and Match formulas for looking them up and keeping them current as time progresses. The tables and calculations are all on the Data sheet.

Check it out here: Link to view
Or make your own copy so you can play around with it.

I welcome your comments and questions. And please feel free to share alike with the other tools available.

Note: It calculates the current percentage of the G, F, S, C, and I funds based on a reference to =Today() in order to show the current quarter. These charts do not automatically update if TSP tweaks the Lifecycle fund allocations in the future.
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 »

UPDATE - February 18, 2019

It appears that in the past couple of days, the TSP has taken down the sharePriceHistory.shtml webpage that once provided fund share prices, and which was the page used in the formula examples above. As a result, any Google Sheets that used that webpage are now broken. Fortunately, the FundPerformance/index.html page can be used instead. To correct your Google sheets, change from the old URL to the new URL. The other parameters (table, row, column) do not need to be changed, just the URL.

New functioning URL:

Code: Select all

https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html
Old broken URL:

Code: Select all

https://www.tsp.gov/investmentfunds/shareprice/sharePriceHistory.shtml
I'm not sure if this broken link is just a short-term glitch that will get fixed in the next couple of days, or if the page is never coming back (at the moment, the page redirects to some other page and then times out), but it looks like the newer page is perfectly good.
donheff
Posts: 22
Joined: Thu Jan 25, 2018 1:19 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by donheff »

Here is the new function I am using to pull today's price for the G Fund from the page. Just modify the table column number for other fuds. To pull yesterday COB price for G:
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 »

donheff wrote: Wed Feb 20, 2019 11:30 am Here is the new function I am using to pull today's price for the G Fund from the page. Just modify the table column number for other fuds. To pull yesterday COB price for G:

Code: Select all

=importxml("https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html","//table[1]/tr[2]/td[7]")
Yup, good work, that's basically the IMPORTXML/XPATH variation of IMPORTHTML. Your way is a little cleaner since it uses only one function instead of two.
User avatar
wassabi
Posts: 514
Joined: Sun Feb 02, 2014 8:06 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by wassabi »

Thanks for the tip. I was able to update my sheets to reflect the new link.

Separately, Vanguard recently converted VTWSX to VTWAX and google does not recognize the ticker (VTWAX). Does anyone know how long it usually takes google to index the ticker so that the Sheets app recognizes it and pulls in the price data?
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 »

wassabi wrote: Wed Feb 20, 2019 1:54 pm Separately, Vanguard recently converted VTWSX to VTWAX and google does not recognize the ticker (VTWAX). Does anyone know how long it usually takes google to index the ticker so that the Sheets app recognizes it and pulls in the price data?
I'm having the same issue with VFSAX - Vanguard FTSE All-World Ex-US Small-Cap Index Fund Admiral.

Might want to try posting in this sorta recent thread: viewtopic.php?t=245411

Lots of others are trying to overcome some deficiencies with GoogleFinance. Not sure how long it takes for Google to pick up new funds. In the meantime, this usually works:

Code: Select all

=importxml("https://www.bloomberg.com/quote/VFSAX:US", "//span[@class='priceText__1853e8a5']")
In the formula above, replace VFSAX with your own ticker. Seems that Bloomberg has these new funds.
User avatar
wassabi
Posts: 514
Joined: Sun Feb 02, 2014 8:06 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by wassabi »

hoppy08520 wrote: Wed Feb 20, 2019 2:27 pm
Lots of others are trying to overcome some deficiencies with GoogleFinance. Not sure how long it takes for Google to pick up new funds. In the meantime, this usually works:

Code: Select all

=importxml("https://www.bloomberg.com/quote/VFSAX:US", "//span[@class='priceText__1853e8a5']")
In the formula above, replace VFSAX with your own ticker. Seems that Bloomberg has these new funds.
That works! :thumbsup :thumbsup
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 »

UPDATE - July 1, 2020

For anyone who is scraping data from the TSP share price history webpage (https://www.tsp.gov/InvestmentFunds/Fun ... index.html), you'll need to update your formulas because the webpage changed.

The TSP recently updated the share price webpage to include the new Lifecycle funds with 5-year retirement dates, and that change threw off the column numbers for each fund. The new column numbers are updated in the OP. You'll need to change your spreadsheet to reflect this change. The previous column numbers were 7, 8, 9, 10, 11 for G, F, C, S, I. New column numbers after the update are 12, 13, 14, 15, 16. The Lifecycle fund columns also changed as new ones were introduced, and the L2020 fund has been retired. See updated OP with information.
Cash
Posts: 1562
Joined: Wed Mar 10, 2010 10:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

Thanks for the update! I would have been wondering what was going on.
ma21n2
Posts: 157
Joined: Fri Sep 28, 2018 9:25 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by ma21n2 »

Looks like TSP did a major update on the price history page, so now the script on Google Sheets to pull share price doesn't work for me. When I look at the page source, there's no table anymore. Anyone know how to pull the current share prices for the various funds? Thanks!
https://www.tsp.gov/fund-performance/sh ... e-history/
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 »

ma21n2 wrote: Sat Jul 11, 2020 11:53 am Looks like TSP did a major update on the price history page, so now the script on Google Sheets to pull share price doesn't work for me. When I look at the page source, there's no table anymore. Anyone know how to pull the current share prices for the various funds? Thanks!
https://www.tsp.gov/fund-performance/sh ... e-history/
Oof, looks like the TSP changed the page. I’ll try to figure it out.
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 »

ma21n2 wrote: Sat Jul 11, 2020 11:53 am Looks like TSP did a major update on the price history page, so now the script on Google Sheets to pull share price doesn't work for me. When I look at the page source, there's no table anymore. Anyone know how to pull the current share prices for the various funds? Thanks!
https://www.tsp.gov/fund-performance/sh ... e-history/
As a backup, this non-TSP website has TSP share prices that you can extract on a Google Sheet: https://www.tsptalk.com/tsp_share_prices.php

This gets you the G Fund:

Code: Select all

=INDEX(importhtml("https://www.tsptalk.com/tsp_share_prices.php", "table", 0), 3, 2)
For the other funds, change the "2" in the formula above to 3, 4, 5, 6 for F Fund, C Fund, S Fund, I Fund, respectively.

I've spent some time looking at the TSP's new page and it's really hard to extract anything from it. Looking under the hood, there is a "raw data" URL you can send a GET to https://secure.tsp.gov/components/CORS/ ... download=1 to retrieve raw data, but the response is unformatted.

I'd prefer to use TSP data over a 3rd party, but I can't figure out how to use the Google functions ImportHtml or ImportXML to parse this data.

If I get around to it, I might make a custom Google App Script to parse out the TSP data page (getSharePricesRaw.html) but that will require the Google Sheet to run a script with a custom function, etc. Not necessarily a bad thing but I'd prefer to use the simpler built-in functions, but the new formatting and services on the new TSP page don't seem to leave you any choice. This is really too bad because the older version of the TSP page worked beautifully for years.
ma21n2
Posts: 157
Joined: Fri Sep 28, 2018 9:25 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by ma21n2 »

hoppy08520 wrote: Sat Jul 11, 2020 12:51 pm
ma21n2 wrote: Sat Jul 11, 2020 11:53 am Looks like TSP did a major update on the price history page, so now the script on Google Sheets to pull share price doesn't work for me. When I look at the page source, there's no table anymore. Anyone know how to pull the current share prices for the various funds? Thanks!
https://www.tsp.gov/fund-performance/sh ... e-history/
As a backup, this non-TSP website has TSP share prices that you can extract on a Google Sheet: https://www.tsptalk.com/tsp_share_prices.php

This gets you the G Fund:

Code: Select all

=INDEX(importhtml("https://www.tsptalk.com/tsp_share_prices.php", "table", 0), 3, 2)
For the other funds, change the "2" in the formula above to 3, 4, 5, 6 for F Fund, C Fund, S Fund, I Fund, respectively.

I've spent some time looking at the TSP's new page and it's really hard to extract anything from it. Looking under the hood, there is a "raw data" URL you can send a GET to https://secure.tsp.gov/components/CORS/ ... download=1 to retrieve raw data, but the response is unformatted.

I'd prefer to use TSP data over a 3rd party, but I can't figure out how to use the Google functions ImportHtml or ImportXML to parse this data.

If I get around to it, I might make a custom Google App Script to parse out the TSP data page (getSharePricesRaw.html) but that will require the Google Sheet to run a script with a custom function, etc. Not necessarily a bad thing but I'd prefer to use the simpler built-in functions, but the new formatting and services on the new TSP page don't seem to leave you any choice. This is really too bad because the older version of the TSP page worked beautifully for years.
Thanks. Looks like getSharePrice.html returns a table in HTML format. This gets me the latest price of the G fund:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,2)
And this gives me the as of date for the price:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,1)
For F fund, you'd just change "G=1" to "F=1", etc.
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 »

Thanks. Looks like getSharePrice.html returns a table in HTML format. This gets me the latest price of the G fund:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,2)
And this gives me the as of date for the price:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,1)
For F fund, you'd just change "G=1" to "F=1", etc.
This worked perfectly! You are awesome.
User avatar
TimeRunner
Posts: 1662
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'm bummed about the table change too as it really messed up my Google sheets. I found another page at TSPTalk that could work, but it's strange in that every row is a new table. The first row with prices is table 9, previous day price is table 10. https://www.tsptalk.com/tracker/tsp_fund_price.php

Would prefer to scrape the authoritative TSP page for current and yesterday's price!

EDIT: I see the problem was solved while I wrote this. Awesome! :beer
EDIT2: Use "Linc=1" for L Income Fund, "L2030", etc. for the rest of the L's.
One cannot enlighten the unconscious.
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 »

ma21n2 wrote: Sat Jul 11, 2020 1:09 pm Thanks. Looks like getSharePrice.html returns a table in HTML format. This gets me the latest price of the G fund:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,2)
And this gives me the as of date for the price:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,1)
For F fund, you'd just change "G=1" to "F=1", etc.
Nice! Thank you for the solution.
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 »

ma21n2 wrote: Sat Jul 11, 2020 1:09 pmThanks. Looks like getSharePrice.html returns a table in HTML format. This gets me the latest price of the G fund:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,2)
And this gives me the as of date for the price:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,1)
For F fund, you'd just change "G=1" to "F=1", etc.
TimeRunner wrote: Sat Jul 11, 2020 1:35 pm EDIT2: Use "Linc=1" for L Income Fund, "L2030", etc. for the rest of the L's.
I just have to give some serious credit to ma21n2 figuring this out. :sharebeer

The new TSP share price webpage he found https://secure.tsp.gov/components/CORS/ ... rices.html is not publicly listed anywhere. It doesn't appear in the network requests if you inspect the other TSP webpages. It's totally hidden. I don't know how he found it. I suspect he looked at the other webpage I listed earlier (https://secure.tsp.gov/components/CORS/ ... download=0) and thought, "Hmm, let me browse this parent directory and see if I find anything?" And the parent directly shows all the files in it, so he found the one webpage in there getSharePrices.html that we need, and then he also reverse engineered it to figure out the query string trick to pass "G=1", "F=1", etc. Or maybe he just thought, "Let me take 'Raw' out of the web page and see what happens." However he stumbled upon this, I don't know, but I would've never figured this out.

ma21n2 must be a hacker in real life. Or he works at the TSP so he has insider knowledge :wink:

And credit to TimeRunner figuring out the other querystring parameters for the Lifecycle funds.
User avatar
LadyGeek
Site Admin
Posts: 72115
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: TSP share price in Google Sheets like GoogleFinance

Post by LadyGeek »

It's not a hack. Consider that those functions are needed by tools external to the site.

Since they're not requiring a login account, it's open-access to all by design. You just need to look for it. :wink:

There are a number of helpful functions: Index of /components/CORS

Go up one level: Index of /components

Tip: Files ending in .pl are Perl scripts.

Update: See the original definition of "hacker" below. Also, those functions are used by other scripts (see below for use in the .js files.).
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
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 »

LadyGeek wrote: Sun Jul 12, 2020 10:36 am It's not a hack. Consider that those functions are needed by tools external to the site.

Since they're not requiring a login account, it's open-access to all by design. You just need to look for it. :wink:

There are a number of helpful functions: Index of /components/CORS

Go up one level: Index of /components

Tip: Files ending in .pl are Perl scripts.
Well, I use the term “hacker” as in someone who is good at figuring out things, things that aren’t documented or explained anywhere. Hacker doesn’t necessarily mean illegal, or unauthorized, it can simply mean you have a lot of ingenuity. People often use the verb “hack” to say that you figured your way around a problem using unexpected ways. I doubt the TSP added these pages expressly for 3rd parties, or else they’d have documented them. So it’s a clever hack to reverse engineer these pages to get what you need from it. A skilled illegal hacker also has these skills but you can also be a plain old hacker who doesn’t do anything illegal.
Last edited by hoppy08520 on Sun Jul 12, 2020 11:20 am, edited 1 time in total.
ma21n2
Posts: 157
Joined: Fri Sep 28, 2018 9:25 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by ma21n2 »

I got "getSharePrices.html" and "G=1" by looking at www.tsp.gov/assets/js/ajaxFetch.js, which is referenced when you do "View Page Source" on www.tsp.gov/fund-performance/share-price-history.

ajaxFetch.js also tells you "https://secure.tsp.gov/components/CORS/"
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 »

ma21n2 wrote: Sun Jul 12, 2020 11:19 am I got "getSharePrices.html" and "G=1" by looking at www.tsp.gov/assets/js/ajaxFetch.js, which is referenced when you do "View Page Source" on www.tsp.gov/fund-performance/share-price-history.

ajaxFetch.js also tells you "https://secure.tsp.gov/components/CORS/"
Ah, nice work :D I didn’t think to look in the js files.
Cash
Posts: 1562
Joined: Wed Mar 10, 2010 10:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

ma21n2 wrote: Sat Jul 11, 2020 1:09 pm Thanks. Looks like getSharePrice.html returns a table in HTML format. This gets me the latest price of the G fund:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,2)
And this gives me the as of date for the price:

Code: Select all

=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?G=1", "table", 1), 2,1)
For F fund, you'd just change "G=1" to "F=1", etc.
Thanks!!
bonez182
Posts: 2
Joined: Tue Feb 20, 2018 9:17 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by bonez182 »

Has anyone got the share prices to return correctly?

This function
=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/ ... ml?L2025=1", "table", 1), 2,2)
returns 0 for the price.

L2035, L2045, L2055, L2060, L2065 are also returning 0.

Thanks.
User avatar
TimeRunner
Posts: 1662
Joined: Sat Dec 29, 2012 9:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

bonez182 wrote: Tue Jul 14, 2020 3:57 pm Has anyone got the share prices to return correctly?

This function
=INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/ ... ml?L2025=1", "table", 1), 2,2)
returns 0 for the price.

L2035, L2045, L2055, L2060, L2065 are also returning 0.

Thanks.
Same result here - it's as if they forgot to update for the new L funds. The first day, the share prices for all new funds were blank on the share price history page, and the table that pops up when you hover over the share price graph did not include the new funds. Yesterday those two bugs were fixed. Hope they fix this one.

EDIT: This bug can also be demonstrated by clicking on the Download Share Prices button and looking at the resulting CSV file, which has blank share prices for the new L funds (edit date 7/15/2020)
One cannot enlighten the unconscious.
User avatar
TimeRunner
Posts: 1662
Joined: Sat Dec 29, 2012 9:23 pm
Location: Beach-side, CA

Re: TSP share price in Google Sheets like GoogleFinance

Post by TimeRunner »

As of 7/21/2020, TSP has fixed the bug/omission with inability to get L2025, L2035, etc (new fund) share prices via share price download button and Index(ImportHTML(xxx) function as described upthread. :beer
One cannot enlighten the unconscious.
StewedCarrot
Posts: 14
Joined: Sun Feb 09, 2020 1:34 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by StewedCarrot »

Thank you hoppy08250 and ma21n2 for finding the table and updating the solution!

My minor tweak is to "concatenate" the URL to point to a specific cell which determines the fund price returned:

Code: Select all

=INDEX(ImportHtml(CONCATENATE("https://secure.tsp.gov/components/CORS/getSharePrices.html?", A2,"=1"), "table", 1), 2,2)
Where A2 is the cell indicating G, F, C, S, or I.

The getSharePrice URL is easier than the previous method.
:happy
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 »

StewedCarrot wrote: Sun Aug 09, 2020 9:51 am Thank you hoppy08250 and ma21n2 for finding the table and updating the solution!

My minor tweak is to "concatenate" the URL to point to a specific cell which determines the fund price returned:

Code: Select all

=INDEX(ImportHtml(CONCATENATE("https://secure.tsp.gov/components/CORS/getSharePrices.html?", A2,"=1"), "table", 1), 2,2)
Where A2 is the cell indicating G, F, C, S, or I.

The getSharePrice URL is easier than the previous method.
:happy
Thanks, I am doing something similar:

Code: Select all

 =INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?" & LEFT(A6,1) & "=1", "table", 1), 2, 2)
In my sheet cell A6 (referenced in formula above) is the fund name like “G Fund”. Instead of CONCATENATE, I combine with the “&”. You could do something similar with the LifeCycle funds (although the Income fund must be exactly “Linc”).
StewedCarrot
Posts: 14
Joined: Sun Feb 09, 2020 1:34 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by StewedCarrot »

hoppy08520 wrote: Sun Aug 09, 2020 10:08 am Thanks, I am doing something similar:

Code: Select all

 =INDEX(ImportHtml("https://secure.tsp.gov/components/CORS/getSharePrices.html?" & LEFT(A6,1) & "=1", "table", 1), 2, 2)
In my sheet cell A6 (referenced in formula above) is the fund name like “G Fund”. Instead of CONCATENATE, I combine with the “&”. You could do something similar with the LifeCycle funds (although the Income fund must be exactly “Linc”).
Didn't know the "&" could do that, 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 »

Hello,
Do you all save all your financial assets information on google, in the cloud? Or is there a way to keep it locally and view/manage the local copy?
I prefer to not have it on the cloud... I tried to download the Excel version of the Sheets document and manipulate it using Excel on my laptop, however, ImportHTML had all kinds of issues in Excel and was showing errors.

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 »

To answer myself, I ended up doing something like this-
https://www.vertex42.com/ExcelTemplates ... uotes.html

Published a google sheet on the cloud with public data and then imported the info from sheets into my private excel spreadsheet on the laptop.
Cash
Posts: 1562
Joined: Wed Mar 10, 2010 10:52 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by Cash »

Did they change the location again? I'm getting an error now...
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 »

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.
CaesarVincentius
Posts: 2
Joined: Fri Jan 01, 2021 3:12 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by CaesarVincentius »

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
gtwhitegold
Posts: 500
Joined: Fri Sep 21, 2012 1:55 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by gtwhitegold »

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
Thanks a bunch. I spent a little bit of time last night trying to figure this out, but I figured that someone smarter than me would get it. I'm kind of surprised that it was this quick though.
User avatar
skow
Posts: 135
Joined: Fri Mar 09, 2007 12:23 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by skow »

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
Hello CaesarVincentius, thanks for your help figuring this out. Very impressive. Is there a way to tweak the formula to have it return the previous day's price as well so I can calculate the daily change? Happy New Year, Skow
CaesarVincentius
Posts: 2
Joined: Fri Jan 01, 2021 3:12 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by CaesarVincentius »

skow wrote: Fri Jan 01, 2021 9:03 pm
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
Hello CaesarVincentius, thanks for your help figuring this out. Very impressive. Is there a way to tweak the formula to have it return the previous day's price as well so I can calculate the daily change? Happy New Year, Skow
Sure, this grabs all quotes over the past 5 days and then selects the latest one. To get the previous quote it should be changed to:

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)
It is just changing from the last row to the 2nd to last row.
User avatar
skow
Posts: 135
Joined: Fri Mar 09, 2007 12:23 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by skow »

Thank you!
Post Reply