Updated TSP screen scraping function

[donheff]

I have screen scraped TSP prices for my Google Spread Sheet for quite some time. About a year ago or so they changed their tables and I could no longer figure out how to import the xml data. Cathy63 helped me out and I got it working again. A couple of weeks ago they added new lifecycle funds and altered the tables. I was able to figure out how to modify my import statement to reference the new table columns. But now they changed the page structure and the table structure and I can't figure it out.

Cathy63, if you are out there can you take a look?

This is the function that was working until last week:

=importxml("https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html","//table[1]/tr[2]/td[12]")

This is a modified function that uses the new page but doesn't work:

=importxml("https://www.tsp.gov/fund-performance/share-price-history/","//table[1]/tr[2]/td[12]")

The page on the tsp site is: /fund-performance/share-price-history/

I will post this as is and see if this stuff displays properly - its been a while and I can't remember what I needed to do to get this stuff to show up properly on erdotorg.

[cathy63]

Well, this is a pain! They have changed the way they bring the data into the page, and I don't think you can scrape it directly any more. It's now in a plain text/CSV file and I don't see a way to get a single number without importing the whole thing. Maybe there is someone else who has a different idea of how to do this.

This formula will bring in yesterday's prices for all the funds: =importdata(concatenate("https://secure.tsp.gov/components/CORS/getSharePricesRaw.html?InvFunds=1&startdate=",year (today()-1),text(month(today()-1),"00"),text(day(today()-1),"00")))

If you put it in a new tab, say it's named Sheet2, and you want the G Fund's price, then you can just copy the cell you're interested in as =Sheet2!B2

Here's a screenshot of how it looks, just in case some characters don't show up correctly above.

If you want the numbers for today, then just change the three places where I put "today()-1" to "today()". I used yesterday to test this because there was no data available for today yet.

[donheff]

Thank you. I figured they would eventually break things. I will fool around with this a bit. If worse comes to worse I can just put a proxy fund ticker into my speadsheet which I did in years past. I don't need precision.

[donheff]

Thank you Cathy. Your approach works fine. I will have to send you a retainer.

[zinger1457]

It's been awhile since I looked at the TSP site, it certainly has changed a lot recently. Are you importing the prices for all the TSP funds into your spreadsheet or just the ones you own? Trying to understand the benefit of importing that data. I just look up the latest share price for the TSP Funds I have (currently only two) and copy/paste that into my spreadsheet, takes about 15 seconds.

[kobedog]

I also ran into the issue of importing TSP share prices with the new TSP web page. Found this thread on Bogleheads. Used the command in this thread and it worked!

https://www.bogleheads.org/forum/viewtopic.php?t=141547

[donheff]

Using Cathy’s function, I am importing the previous day’s table of prices into a separate sheet (tab). I then reference the few cells I want in my primary sheet. The primary sheet lists all of my wife and my holdings and updates prices at the end of day. I update the share numbers occasionally to account for dividends and sales. Otherwise it auto updates.

[cathy63]

The BogleHeads solution also works, and it doesn't require importing into an extra tab, so it might be easier to use.

[donheff]

I inserted Bogleheads code for a couple of funds and left Cathy's approach for others. That way I have reminders of both.

Thanks to both of you.