Excel Hyperlink

**Diesel** · 11-19-2008, 12:34 PM

I'm just about finished with my excel spreadsheet but have a few questions for those with more experience with excel, especially related to the TSP.

I'd like to hyperlink the share values from an internet site so when I open the document, the excel spreadsheet automatically goes to the internet and pulls the information, placing it into the appropriate cell. Just like the TSP Tracker tool available in the utilities section. I know I can figure out the hyperlink function, but where to pull the data from for the G,F,C,S, and I fund?

Secondly, I'd like to be able to open my document and have it automate the following process.

I manually enter my monthly contribution.
The spreadsheet caluclates my 5% matching, based on my base pay, and adds it to my monthly contribution and then distrubutes the monies across the different funds dependent upon my allocation percentages minus the TSP handlers fee.

If someone has this type of spreadsheet already made, I'm not proud, I've pulled my hair out for a few days now. PM me and share the wealth

Thanks in advance.

**tsptalk** · 11-19-2008, 03:05 PM

Originally Posted by Diesel

I know I can figure out the hyperlink function, but where to pull the data from for the G,F,C,S, and I fund?

Why not the TSP website? http://www.tsp.gov/rates/share-prices.html

**Diesel** · 11-19-2008, 03:28 PM

I need to study up on how to pull that data from this location. I've never done any programming or html editing other than basic stuff. Ideally this would be the place I'd pull it from.

**tsptalk** · 11-19-2008, 03:34 PM

Oh, I see. I thought that's what you meant by hyperlink function.

Jayhawker would be able to help, but I haven't seen him here in a couple of weeks. I'm sure there are others too. I have to check my old archived files for the one someone wrote for me a few years ago.

**peterson82** · 11-19-2008, 06:12 PM

Diesel, you just need to create a web query pointed at the tsp website's fund data.

I threw together an example of what you wanted. The 'DATA' sheet will automatically refresh when you open it up. To view the web query goto the DATA sheet and click on cell A1. Then goto the Data menu item 'Import External Data' and then 'Edit Query'. You will see that there is a green check mark next to the table that I wanted to import. And I set it to update every time I open the project.

Take a look, it is very basic, but it should do the things you were asking about. If you have any questions I will try and answer! Good Luck.

P82

**tsptalk** · 11-19-2008, 06:30 PM

Thanks P82!

**SteveTSP** · 11-19-2008, 07:22 PM

Diesel,
I am not an excel power user, so I am not able to offer any real help to orginate the spreadsheet. But I can help troubleshoot if you get a version done that automates most of the tasks you desire. Drop me a PM and your latest version and I will try to ring it out at the same time and hopefully I can benefit by stop doing these things manually everyday.
Thanks, Steve

**Diesel** · 11-20-2008, 09:21 AM

Steve,

Thanks for the assistance. I'll PM you my email address and we can work on this together. I'm currently in Iraq and on the military domain. I have no access to the commerial FTP sites that I could possibly upload this file to. I believe it will be much easier to see what I'm doing if I leave my data included. Please keep in mind my loss is only on paper and I have 20 more years of Civil Service left. It's ugly. Plenty of time to make up the losses I've had over the last 3 years in the C,S, and I funds.

I'm a rookie when it comes to predicting the market. I'd rather stick with performing my IFT's, their amounts, on what the market IS doing.

In the past, I've waited until the QTRLY STATEMENTS are out before plugging in all the data in on the various funds manually. So I'm missing this QUARTER's information with the exception of the last pay period.

I'll be studying up on these macro's that were included on the above file and try to incorporate them for exactly what I'm attempting to do.

Thanks.

**Diesel** · 11-20-2008, 09:46 AM

Originally Posted by peterson82

Diesel, you just need to create a web query pointed at the tsp website's fund data.

I threw together an example of what you wanted. The 'DATA' sheet will automatically refresh when you open it up. To view the web query goto the DATA sheet and click on cell A1. Then goto the Data menu item 'Import External Data' and then 'Edit Query'. You will see that there is a green check mark next to the table that I wanted to import. And I set it to update every time I open the project.

Take a look, it is very basic, but it should do the things you were asking about. If you have any questions I will try and answer! Good Luck.

P82

Thanks for this. It helps and I've got the data it pulls filling out portions of my spreadsheet. I see how to do this now, thanks.

The Macro's you incuded were interesting as well. I'm trying to pull data from the web and excel help on this ability. Lots of learning to do on Visual Basic. The BUY SHARES is a little confusing. I'd like to be able to place my monthly base pay in the cell. 5% would be pulled from this and added to the NET minus the service fee.

Any help would be appreciated. Do we all have access to the server FTP so I could upload?

**ChemEng** · 11-20-2008, 12:16 PM

You can check out my spreadsheet here for a way to do exactly that in Excel. (It also estimates the I fund price throughout the day.)

**peterson82** · 11-20-2008, 12:54 PM

Yeah, entering how much you are contributing says nothing about your total pay. My off-the-top of my head answer was to include how much the contribution was as a percentage of your yearly salary so I could do the equation

yearly salary = (contribution / percent of yearly salary)
5% match = (yearly salary / 26) * .05

Sorry for the confusion I just whipped it up really quick as a little example.
I do like the idea of having a field you can enter in the yearly salary. And I will include that now.

**peterson82** · 11-20-2008, 02:41 PM

Okay, I redid the programming. As far as the service fee goes, I was not sure how to calculate this. I did some research and it seems like it is based on an expense ratio. For 2007 the expense ratio was 1.5 Basis points or .015%. This means for every $1000 the fee is 15 cents. Based on this, I made the service fee equation .00015 of the amount contributed at the time. I am not sure if that is correct. (Plus, I don't know what the 2008 expense ratio is.)

Anyway, this version should be easier to understand.

P82