I fund tracker...

[ChemEng]

Here is the spreadsheet I talked about on Friday. It tracks the real-time % changes of the component EAFE funds and then using their EAFE weightings, calculates an overall % change. I found the macro coding online while trying to find a list of the different Yahoo tags and thought I could put it to good use tracking I fund changes.

1. This is done in MS Excel and uses a macro. That means you need to enable the macro when you start up the spreadsheet.
2. It works on Excel versions 2002 or later. If you have trouble loading it, please check your version of Excel to see if this is the problem.

--------------------------------------------------------------------------------------------------------------------
17JAN07--Corrected spreadsheet to track Nikkei 300, instead of Nikkei 225.
02MAR07--Updated spreadsheet given new data about DX.
09APR07--Updated new tsp.gov import bug I found.
02JUL07--Dynamic coded the spreadsheet to fix TSP.gov's table format changing. (This should permanently fix the bug I found on 09APR07.)
12JUL07--Found opportunity to speed up coding.
24AUG07--Added cell to allow for copy+paste of timed updates.
24AUG07--Updated the composition of the I fund based on new data from TSP website.
23DEC07--Changed CAC 40 symbol from ^FCHI to ^CAC.
16FEB08--Changed Ireland symbol to ^I:IEDOW and Portugal symbol to ^I:PTDOW.
22APR08--Updated symbols for Japan, Greece, and Ireland.
20OCT08--Updated program to reflect the new location of the Index data.
27JAN09--Addressed TSP.gov redirect issue.
03FEB09--Corrected sort method to work with earlier Excel versions.

I fund lookup (http://workingmansblog.com/TSP/I_fund_lookup_%2803FEB09%29.xls)

[fabijo]

Thanks, Chem! This is a nice little program.

[ayla]

Thanks ChemEng! I've been trying to get something going on this and was not able to.

[ChemEng]

Im glad that you found this utility useful! Although Im still not certain that Im tracking the right symbols on the spreadsheet. (Note I did update it this morning to track the Nikkei 300 instead of the Nikkei 225.)

Is there anyone out there that could double check the listing to make sure its tracking the right ones?

[ebbnflow]

ChemEng, I track these major world indices:

^N300,^FTSE,^FCHI,^SSMI,^GDAXI^,AORD,^MIBTEL,^IBEX ,^AEX,^OMXSPI,^HSI,^FIDOW,^BFX,^IEDOW,^STI,OMXC20. CO,^OSEAX,^GRDOW,^ATX,^PTDOW,^NZ50 (http://finance.yahoo.com/q/cq?s=^N300,^FTSE,^FCHI,^SSMI,^GDAXI,^AORD,^MIBTEL, ^IBEX,^AEX,^OMXSPI,^HSI,^BFX,^FIDOW,^IEDOW,^STI,OM XC20.CO,^OSEAX,^GRDOW,^ATX,^PTDOW,^NZ50)

You may have to check if you got FINLAND (^FIDOW 1.40%) included. The above link lists the indices from top to bottom according to its countries' weight. :)

[Rod]

Great spreadsheet! Thanx for sharing.

Perhaps with your excel expertise, you could come up with a spreadsheet that calculates/estimates the (I) Fund price increase/decrease for the day?

[ChemEng]

Updated I fund lookup spreadsheet today. Will post here any updates that get made to it.

[Rod]

This is a good place for a sticky!:cool:

[ChemEng]

Updated new tracker to fix a new tsp.gov import bug I found.

[wv-girl]

Updated new tracker to fix a new tsp.gov import bug I found.

ChemEng,

I re-downloaded the tracker but still only shows to April 3rd. Did I miss something.

Thx for all your hard work.

:)

[ChemEng]

ChemEng,

I re-downloaded the tracker but still only shows to April 3rd. Did I miss something.

Thx for all your hard work.

:)

Give it a try again. I uploaded the latest copy of the tracker again.

[wv-girl]

Thanks that worked.

Happy trading.

[ChemEng]

Updated spreadsheet again. Thought of a simple way to compensate for the changes in variable table formats that TSP.gov uses around holidays. Let me know if this works!

[James48843]

Chem:

Thanks for the update. I had to go back to page one of this thread to find the updated version. It works well.

Thanks again.

[ChemEng]

Chem:

Thanks for the update. I had to go back to page one of this thread to find the updated version. It works well.

Thanks again.

Heres the I fund lookup not on the front page. :D

1733

[ChemEng]

Updated spreadsheet. Found a way to speed it up if you're like me and use it multiple times a day. Enjoy!

[T2034]

Thank you, I used it all the time. Keep up the good work!!

[wv-girl]

Thanks for all of your hard work. I use this often through the day. Even after noon to check on the $ direction.

Debbie

[ayla]

This latest version works with Excel 2000 (my copy anyway). The older versions (at least the last ones I tried) did not so wanted to let others know in case they thought they wouldn't be able to use this wonderful application. Thanks ChemEng!

[ChemEng]

Updated the spreadsheet to include a cell to enable quick and easy estimates to be posted in the I-fund thread. The process is simple.

Press Get Data! button.
Point to cell G36.
Copy that cell.
Paste data into message box in 350z's I fund thread.
Post message.

One other small update. If there is a FVC for todays markets, then you enter it into cell G34. (It will add itself into the estimate text automatically.)

Let me know if you have any problems.

[ChemEng]

Updated the composition of the I fund based on new data from TSP website. The latest version of this sheet should have ^FTSE as the first index instead of ^N300.

[RPM]

ChemEng:

Thanks very much for the spreadsheet. I have a favor to ask though. In your macro updating the share prices and I fund current %, can you also pull in the % for the C (^GSPC) and S (^DWCPF)funds? These are the 2 funds I used to track C and S. I can handle the simple spreadsheet, but have no idea how to do macros, especially to pull in real data!

Thank you mucho! :)

[catt1]

I agree. Keeping an Eye on C and S funds all in the same spreadsheet would be helpful.

[ChemEng]

I agree. Keeping an Eye on C and S funds all in the same spreadsheet would be helpful.

Thanks for the comments. Ill add this feature in the next version of the tracker. It will probably be next week before I can get to it though, Im pretty heavily engaged with work and family (wife's due in 4 weeks!!) right now.

[pogo]

thanks ChemEng-----pogo--- used it everyday before work (3:00 am)

[wv-girl]

Thanks for the comments. Ill add this feature in the next version of the tracker. It will probably be next week before I can get to it though, Im pretty heavily engaged with work and family (wife's due in 4 weeks!!) right now.
Congrats on the new addition to your family. Your first?
BTW--thanks from me also for your spreadsheet. Use it everyday.
Debbie:)

[nnuut]

Thanks for the comments. Ill add this feature in the next version of the tracker. It will probably be next week before I can get to it though, Im pretty heavily engaged with work and family (wife's due in 4 weeks!!) right now.
4 weeks!! Wow, Congratulations! It won't be long now. Nothing but fun from now on.:D

[RPM]

Thanks for the comments. Ill add this feature in the next version of the tracker. It will probably be next week before I can get to it though, Im pretty heavily engaged with work and family (wife's due in 4 weeks!!) right now.

Congratulations! Enjoy them before they grow old on you :laugh:

[Rod]

It seems ^FCHI is no longer a valid ticker symbol.

Do you have an update?

Thank you!:)

[ChemEng]

It seems ^FCHI is no longer a valid ticker symbol.

Do you have an update?

Thank you!:)

Thanks for the heads up. Sorry its taken me a few days to get back with you, but Ive been enjoying paternity/Christmas leave. Here's the update.

2873

[Rod]

Thanks for the heads up. Sorry its taken me a few days to get back with you, but Ive been enjoying paternity/Christmas leave. Here's the update.

2873

Not a problem, my friend!:)

Thank you, and Merry Christmas!

[ChemEng]

Tracker is updated to show correct symbols for Ireland and Portugal.

I fund tracker. (http://workingmansblog.com/TSP/I_fund_lookup.xls)

[ChemEng]

Sorry about that. The front page was right, but I accidentally added a "." at the end of the hyperlink in the thread that was screwing things up.

Thanks for the heads up!

[RPM]

ChemEng:

It seems that the symbols are changed again, I got 3 that did not work, including Nikkei... Would appreciate if you can update it when you have the time. TIA.

[ChemEng]

Im not exactly sure what is going on. There most definitely is a ^N300 on Yahoo. So I am guessing it is a temporary glitch with yahoo finance. I have updated the funds it looks for in the mean time to give something close. If it continues to act up, I think it may have to find a different way to go at getting the data (that isnt immediately apparent to me right now).

I fund lookup (http://workingmansblog.com/TSP/I_fund_lookup.xls)

Good luck!

[The_Technician]

Im not exactly sure what is going on. There most definitely is a ^N300 on Yahoo. So I am guessing it is a temporary glitch with yahoo finance. I have updated the funds it looks for in the mean time to give something close. If it continues to act up, I think it may have to find a different way to go at getting the data (that isnt immediately apparent to me right now).

I fund lookup (http://workingmansblog.com/TSP/I_fund_lookup.xls)

Good luck!
U must be having other problems other than the symbols...the Yahoo symbols I use have been reporting this morning as usual....been that way for about a month or so....I don't detect any symbol change...

[ChemEng]

U must be having other problems other than the symbols...the Yahoo symbols I use have been reporting this morning as usual....been that way for about a month or so....I don't detect any symbol change...
That is why I am thinking there is something going on with the way Yahoo is broadcasting its CSV files for specific markets. There most certainly is a ^N300 index--you can view it in Yahoo Finance. But when you try to access the CSV, you get an error. If you do the same exact approach with ^N225, it works.

The only thing I can think of is that theres an issue on Yahoo's end.

[luv2read]

never mind...looks good now!

[ChemEng]

The address for the Index data changed, so I updated the program accordingly.

You need to update to the latest version below to continue use.

I fund lookup (http://workingmansblog.com/TSP/I_fund_lookup.xls)

[luv2read]

It's not working this morning, I get massive errors. Perhaps needing another update?

Thanks!

[pogo]

the tsp board----playing games with us. we need Chem

Eng to the rescue

[ChemEng]

I have a solution that I am working on. I will try to get the updated file tonight. (I am at a conference this week so it may have to get pushed back to the weekend.)

[ChemEng]

Everyone please try this new version of the "I fund lookup" tool. I think I got a fix in place for the tsp.gov redirect. (This is working for Excel 2007. I havent had a chance to run it on earlier version of excel.)

I fund lookup (http://workingmansblog.com/TSP/I_fund_lookup_%2827JAN09%29.xls)

Isn't it funny how a certain other lookup tool breaks in exactly the same way that this one did at exactly the same time. Hrrmmm... I wonder why that is. If I were a betting man, I would imagine a fix for that one tool would also magically happen shortly after I post this one.

[pogo]

why does the tsp board have to mess with us? i had everthing set up with a push of a button and then they screw my whole set-up. ChemEng you are the MAN!!!!!!! thank you

[ChemEng]

If you are able to get this update to run with your excel, please reply in the thread with what version of excel you are using. That will help me out when I try to troubleshoot any problems. Thanks!

[JTH]

1 of 2 & 2 of 2 PMs sent...JTH :(

[luv2read]

and TODAY....the 10/20/08 version works JUST FINE!!!!:D:nuts:

[ChemEng]

I corrected the error with the previous tracker for versions of Excel before Excel 2007. Thanks to everyone that pointed it out to me. Please try the new download to verify the functionality has returned.

I fund lookup (http://workingmansblog.com/TSP/I_fund_lookup_%2803FEB09%29.xls)

[RPM]

Do you have a new Currency index to use? my DX-Y doesn't seem to work anymore on Yahoo Finance.

Thanks mucho


I corrected the error with the previous tracker for versions of Excel before Excel 2007. Thanks to everyone that pointed it out to me. Please try the new download to verify the functionality has returned.

I fund lookup (http://workingmansblog.com/TSP/I_fund_lookup_%2803FEB09%29.xls)