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
hornet96
Posts: 765
Joined: Sun Nov 25, 2012 6:45 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by hornet96 »

Just FYI, somehow the TSP Talk link is still working and pulling in updated prices. I know it's not the "official" site but it's frankly the only one that has reliably worked for several months now, and thus is the one I use.

https://www.tsptalk.com/tracker/tsp_fund_price.php
User avatar
NoRoboGuy
Posts: 838
Joined: Fri Apr 01, 2011 11:07 pm
Location: Alabama
Contact:

Re: TSP share price in Google Sheets like GoogleFinance

Post by NoRoboGuy »

Looking further the server is not down, but it is not accepting the wget command that previously worked:

Code: Select all

wget -O /tmp/tmpzoho.csv "https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=%22,TEXT(TODAY()-5,%22YYYYMMDD%22),%22&enddate=today%22))"
--2022-06-07 17:05:02--  https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?Lfunds=1&InvFunds=1&startdate=%22,TEXT(TODAY()-5,%22YYYYMMDD%22),%22&enddate=today%22))
Loaded CA certificate '/etc/ssl/certs/ca-certificates.crt'
Resolving secure.tsp.gov (secure.tsp.gov)... 18.67.76.6, 18.67.76.108, 18.67.76.126, ...
Connecting to secure.tsp.gov (secure.tsp.gov)|18.67.76.6|:443... connected.
HTTP request sent, awaiting response... 403 Forbidden
2022-06-07 17:05:03 ERROR 403: Forbidden.
There is no free lunch.
RedLeader
Posts: 4
Joined: Tue Jun 07, 2022 5:53 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by RedLeader »

Can't take the credit for this (and to be honest, can't remember where I found it). "TSP Funds" tab shows a recursive calculation that prioritizes the TSP website, followed by TSP talk, followed by TSPFolio.

https://docs.google.com/spreadsheets/d/ ... =537239301
User avatar
Topic Author
hoppy08520
Posts: 2183
Joined: Sat Feb 18, 2012 11:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

NoRoboGuy wrote: Tue Jun 07, 2022 5:06 pm Looking further the server is not down, but it is not accepting the wget command that previously worked:

Code: Select all

https://secure.tsp.gov/components/CORS/getSharePricesRaw.html
I found that the TSP took down getSharePricesRaw.html in the recent website redesign. Now the relevant page is at:

Code: Select all

https://secure.tsp.gov/fund-performance/share-price-history/
and the URL that used to return data with ImportHtml/ImportXML no longer works. I spent some time trying to figure out how to extract data from the new URL and the services it calls, but I couldn't get it easily and I gave up (at least for now....). The new website makes it more complicated. At least for now, I'm using the backup services shown in https://docs.google.com/spreadsheets/d/ ... =537239301.
User avatar
NoRoboGuy
Posts: 838
Joined: Fri Apr 01, 2011 11:07 pm
Location: Alabama
Contact:

Re: TSP share price in Google Sheets like GoogleFinance

Post by NoRoboGuy »

Apparently they are having issues. That page returns

Code: Select all

Hmmm, something isn’t working. Please try again in a few minutes. If the problem persists you can call the ThriftLine for assistance at 1-877-968-3778.
There is no free lunch.
RedLeader
Posts: 4
Joined: Tue Jun 07, 2022 5:53 pm

Re: TSP share price in Google Sheets like GoogleFinance

Post by RedLeader »

hoppy08520 wrote: Tue Jun 21, 2022 9:22 am
NoRoboGuy wrote: Tue Jun 07, 2022 5:06 pm Looking further the server is not down, but it is not accepting the wget command that previously worked:

Code: Select all

https://secure.tsp.gov/components/CORS/getSharePricesRaw.html
I found that the TSP took down getSharePricesRaw.html in the recent website redesign. Now the relevant page is at:

Code: Select all

https://secure.tsp.gov/fund-performance/share-price-history/
and the URL that used to return data with ImportHtml/ImportXML no longer works. I spent some time trying to figure out how to extract data from the new URL and the services it calls, but I couldn't get it easily and I gave up (at least for now....). The new website makes it more complicated. At least for now, I'm using the backup services shown in https://docs.google.com/spreadsheets/d/ ... =537239301.
Unfortunately ImportHTML and its associated commands won't work because the table of prices is dynamically created and doesn't exist in the HTML. I tried using the ImportXML but ran into issues again because of how they generate the values...basically it's designed to prevent web scraping from various sources. If it stays how the site is currently, it won't work. I might take another stab at it when life calms down in a few weeks.
User avatar
Topic Author
hoppy08520
Posts: 2183
Joined: Sat Feb 18, 2012 11:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

RedLeader wrote: Thu Jun 23, 2022 1:01 pm Unfortunately ImportHTML and its associated commands won't work because the table of prices is dynamically created and doesn't exist in the HTML. I tried using the ImportXML but ran into issues again because of how they generate the values...basically it's designed to prevent web scraping from various sources. If it stays how the site is currently, it won't work. I might take another stab at it when life calms down in a few weeks.
This is what my probing found too. It's like the website was deliberately designed to thwart scraping or DDoS attacks or whatever. When I looked in the browser inspector, it appears that the URL that returns the data to the page was named (at the time I tried it):

Code: Select all

https://secure.tsp.gov/data/getSharePricesRaw_startdate_20220424_enddate_20220524_Lfunds_1_InvFunds_1_download_0.html
which is obviously a dynamically-named URL that will be a moving target.

It would be nice if the TSP could publish a data-only feed or API of the share prices.
User avatar
Topic Author
hoppy08520
Posts: 2183
Joined: Sat Feb 18, 2012 11:36 am

Re: TSP share price in Google Sheets like GoogleFinance

Post by hoppy08520 »

RedLeader wrote: Thu Jun 23, 2022 1:01 pm Unfortunately ImportHTML and its associated commands won't work because the table of prices is dynamically created and doesn't exist in the HTML. I tried using the ImportXML but ran into issues again because of how they generate the values...basically it's designed to prevent web scraping from various sources. If it stays how the site is currently, it won't work. I might take another stab at it when life calms down in a few weeks.
This is what my probing found too. It's like the website was deliberately designed to thwart scraping or DDoS attacks or whatever. When I looked in the browser inspector, it appears that the URL that returns the data to the page was named (at the time I tried it):

Code: Select all

https://secure.tsp.gov/data/getSharePricesRaw_startdate_20220424_enddate_20220524_Lfunds_1_InvFunds_1_download_0.html
which is obviously a dynamically-named URL that will be a moving target.

It would be nice if the TSP could publish a data-only feed or API of the share prices.
User avatar
TimeRunner
Posts: 1845
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 pulling prices from the TSP Folio website. Someone there is probably updating that site by hand. OK with me.

Code for G Fund example:

Code: Select all

=Index(ImportHtml("http://www.tspfolio.com/tspfunds", "table", 1), 2, 2)
One cannot enlighten the unconscious. | "All I need are some tasty waves, a cool buzz, and I'm fine." -Jeff Spicoli
Post Reply