TSPLookup - Introduction, Comments & Questions Thread [Archive]

jayhawker

04-15-2007, 03:18 PM

TSPLookup

Introduction, Comments & Questions

All,

Allow me to introduce you to TSPLookup. TSPLookup is a Microsoft® Excel application that has/does the following:

Gets the market quotes for the TSP F, C and S Fund quotes.
Gets the market quotes for the foreign markets that comprise the EAFE / I Fund
Gets the currency quotes for the Big Five currencies.
Gets the market quotes for up to 12 Yahoo symbols of your choosing.
Estimates the EAFE / I Fund.
TSP Account Growth Projection feature.
A Dashboard that provides:
A quick view of the markets.
A view of your TSP portfolio. You may enter the number of TSP shares you own and then view today’s estimates and the previous days’ results.
The TSP Share Prices for the past 15 market days.
Another access to the Account Growth Projection feature.
Download TSPLookup v1.0 (TSPLookup1.zip) via the link in my signature.

Click the link to go to the download page.
Download the file
Save the zipfile on your local hard drive.
Double click the zipfile to unzip it.
Select TSPLookup1.xls, extract it and save on your local hard drive.
Double click TSPLookup1.xls to open it in Excel.
(The file will be a bit slow to load the first time it is opened on a different PC. Subsequent openings on the same PC will be quite quick.)Will TSPLookup work on your PC?

The application was written in Excel 2002 and will work in 2002 all later versions (2003, 2007)
WILL NOT work in previous versions (Excel 95, 97, 2000 - sorry!)
Contains macros and you MUST enable the macros.
A high-speed Internet connection (cable, T1 or better) is recommended.
Completely safe for use at work and home. TSPLookup is free for you to use, however, I encourage you to support the application with a donation through PayPal®. If you like and use the application, please donate. And, please share TSPLookup with your co-workers and other TSP participants!

Well… that’s about it for the introduction! I’ll post any updates in this thread. Feel free to post your comments or questions here. Also, you may send me a PM or write to me at tsplookup@hotmail.com (tsplookup@hotmail.com) .

Enjoy!

Ed.

Here are some pictures.

This is TSPLookup with the built-in Help comments shown:http://www.tsptalk.com/tsplookup/home.jpg

You can have the Dashboard on top of TSPLookup...http://www.tsptalk.com/tsplookup/dashboard_2b.jpg

or you can have it top of a different workbook (or any Windows application!)http://www.tsptalk.com/tsplookup/tsp_dashboard1.jpg

Here's the Account Growth Projection feature:
http://www.tsptalk.com/tsplookup/growth.jpg

Enjoy!

Ed.

nnuut

04-15-2007, 03:34 PM

Happy Sunday Jayhawker!! This looks really neat, Who came up with this JEWEL?:confused: :D Gotta try it!!:toung:

ChemEng

04-15-2007, 03:48 PM

Anyone that loads a macro without being able to see the VBA that it contains is foolish.

jayhawker

04-15-2007, 04:07 PM

Anyone that loads a macro without being able to see the VBA that it contains is foolish.

I won't argue with that statement (although I do question the sentiment.) However, as the author, I have the right to control the VBA. And, as a Government employee, I would be a fool to include any spyware/malware or other evil code.

The macros in the application are totally safe to run on any PC.

Ed.

ChemEng

04-15-2007, 04:23 PM

I won't argue with that statement (although I do question the sentiment.)

Thats fine. Heres another link to a message on these boards regarding the same thing. http://www.tsptalk.com/mb/showpost.php?p=85132&postcount=255

The macros in the application are totally safe to run on any PC.

Isnt that the same thing every phishing email/keylogging application says?

jayhawker

04-15-2007, 06:21 PM

Thats fine. Heres another link to a message on these boards regarding the same thing. http://www.tsptalk.com/mb/showpost.php?p=85132&postcount=255

Isnt that the same thing every phishing email/keylogging application says?

Ok, you've got me there. :suspicious:

The difference - at least to me - is that I am the author and I'm the one saying so.

Technically, a user can open the application and NOT enable the macros. The user can look around, page down and read additional information and read the disclaimer. They will see my full name. Any user who works for the USPS (as I do) can look up my .gov email address on their ACE workstation in Outlook.

ChemEng, you're concerns have been duly noted. Thank you. All: if you have any concerns whatsoever - don't use the application. Simple as that.

Now seems like a good time to acknowledge that this message board is the most civil board I visit. Rarely is there animosity, backbiting, insults and the like that you see on other message boards. I think it's a credit to Tom, and a credit to the civility and maturity of US Government employees.

Ed.

jayhawker

04-16-2007, 08:06 AM

The download site I stored the file on is blocked here at work. Honestly, I did not anticipate that! :mad: It may be blocked at your work also.

If you want the file and can't get to the download site, shoot me an email to "tsplookup at hotmail dot com" and I'll send it to you.

Thanks,

Ed.

jayhawker

04-16-2007, 02:00 PM

I updated the file at the link at 12:30 PM ET today. There was an error in a value in the Dashboard. The beta-testers (my usually very capable co-workers) have been duly admonished! :mad:

If you downloaded the file before Monday 12:30 PM ET, please download it again to get the latest. It won't happen again! :D

I would like to post the file here on the message board but at just 200kb it is too large to be allowed. So, again, if you want TSPLookup and can't get to the download page, send me a PM here or an email.

Remember to delete your share prices and/or your Account Growth Projection values BEFORE you pass the application on to others. You probably don't want others to see those values!

Thanks,

Ed.

nnuut

04-17-2007, 11:46 AM

Downloaded your spreadsheet today, a real piece of work, thanks for all of your hard work!
Norman:D

charmed855

04-17-2007, 03:49 PM

Ed, thanks for all the hard work you put into this. Really nice tool!

When does the FV calculation update? Right now it says -.13 but I'm assuming that is for yesterday.

jayhawker

04-17-2007, 04:28 PM

Ed, thanks for all the hard work you put into this. Really nice tool!

When does the FV calculation update? Right now it says -.13 but I'm assuming that is for yesterday.

That's right, the -13 shown today is yesterday's FV (correction.) The FV value shown is after the fact (meaning, I'm not making any attempt to predict an FV.) There either was an FV or there was not.

The FV value will update when the updated EAFE value is queried from mscibarra.com. While the new EAFE value is usually available at that website around 15:00 ET, the query won't get the new value until the web page defaults to the current date. This usually happens at about 19:00 ET.

Thanks for the question. And thanks!

Ed.

jayhawker

04-17-2007, 05:10 PM

The FV value will update when the updated EAFE value is queried from mscibarra.com. While the new EAFE value is usually available at that website around 15:00 ET, the query won't get the new value until the web page defaults to the current date. This usually happens at about 19:00 ET.

And... :D :embarrest:

I have to have the updated TSP share prices too! Basically, I'm taking what the actual change in the EAFE was and factoring what the share price change equates to. Then I check the actual change in the TSP share price for the day. IF the difference is more than .03 cents, then I conclude that there was an FV. So... the correct answer to your question is about 20:00 ET. Both the new EAFE and the new TSP share prices are usually available to query at that time.

(Good thing I put good comments to myself in this thing... otherwise I'd soon forget how it works!) :blink:

Ed.

merlin

04-17-2007, 09:08 PM

NIce app Jayhawker.:)

AIF

04-18-2007, 06:10 AM

jayhawker

04-18-2007, 09:22 AM

Is something wrong with the file? It will not download properly for me! I was able to download it yesterday but the corrected version is not downloading properly. The site says its a 181.41 KB file but I can only download about 134 KB.

Since I can't get to the download site from here at work, I asked a friend to download the file, unzip it, and open it. He did so successfully.

There may have been a problem with the server the file is stored on. Please try again. And I will check the process tonight and change the link if necessary.

I'm working on finding a new, dependable home for the file that can be reached by everyone all the time. :)

Ed.

AIF

04-18-2007, 10:06 AM

There may have been a problem with the server the file is stored on. Please try again. And I will check the process tonight and change the link if necessary.

It worked this time. I think it was a temporary problem.
Thank you,
AIF

jayhawker

04-18-2007, 11:29 AM

In an effort to keep the many TSPLookup users informed (and to bump this thread to the top in a most self-serving way), I’m instituting the “Infrequently Asked Question of the Day.” I’ll post the IAQD after the IFT rush so as not to (further) annoy users. So, without further ado:

Infrequently Asked Question of the Day #1

Love the spreadsheet! Awesome job! But I’m already sick and tired of the cat that pops up every time I update from the main spreadsheet! Can I change the cat to something else?

Yes! You can change the office assistant.

1) Select the Excel Help from the top menu
2) Select Show the Office Assistant
3) Move your cursor to the now visible Office Assistant and right-click
4) Select Choose Assistant
5) Click the <Next> and <Back> buttons to view the selections.
6) Select OK when viewing the assistant you want to use.

I chose Links (the cat) as the default because I personally like cats. And the timing of how the cat enters and exits seemed to work well for the spreadsheet. Rocky (the dog) works pretty well, as does Clippit (the much-maligned paper-clip) and Merlin (the magician.) F1 (the robot/transformer looking thingie) is slow to enter and to exit. But any assistant you choose will appear and disappear when you update.

Thanks for your question! Ed.

fabijo

04-18-2007, 05:08 PM

It shows the paperclip guy for me. I'm pretty tired of that guy.

jayhawker

04-18-2007, 05:26 PM

It shows the paperclip guy for me. I'm pretty tired of that guy.

Interesting. I make an attempt to determine if Clippit was your current office assistant then change it to Links. You must have a later version of Office than I do.

Oh well. You can change the Office Assistant using the instructions above. Try the cat! :D

fabijo

04-18-2007, 10:39 PM

jayhawker

04-20-2007, 07:58 AM

By the way, your Excel skills are excellent. It is a masterpiece. I should probably do more research about using macros. I could probably cut down my file sizes considerably by using a macro instead of using the same statements over 14000 times!

Thanks fabijo!

I looked at your monkey files... 57 years worth of data is a LOT of data. I'm still studying it - trying to figure out what it all means. On first glance, I don't see how adding macros would help. Maybe you could cut it down to the past 20 years or so! :)

Ed.

jayhawker

04-20-2007, 08:02 AM

All:

You can expect an update to TSPLookup on Monday or Tuesday. A couple of users have made good suggestions that I want to implement. I'm listening! If YOU have any suggestions or have found any problems, please send me a pm or post here.

Thanks,

Ed.

jayhawker

04-20-2007, 11:51 AM

Today's Infrequently Asked Question of the Day. (You get two today because I was "indisposed" yesterday.)

#2
The built-in help states there are 12 spots to enter Yahoo symbols I want to track. There are actually 13. Can’t you count???

Yes, I can count. I know how many are there. I’m superstitious. I couldn’t bring myself to say there were – that number you wrote. And you can use all of them.

#3
Cool spreadsheet! Why are some of the currencies rates quoted in US$ per foreign currency units and others quoted in foreign currency units per US$?

Good question! It’s just the way it’s always been done. From the Federal Reserve Bank of New York website: “We report some rates in U.S. dollars per foreign currency unit and others in foreign currency units per U.S. dollar, following a long-standing convention.”

I could have just as easily shown the currencies in the same relationship to the US$. I didn’t simply because I didn’t want to go against convention.

Thanks for the questions. Ed.

tsptalk

04-23-2007, 10:56 AM

Jayhawker now has a separate page where you can read more information and download the utility... www.tsptalk.com/tsplookup (http://www.tsptalk.com/tsplookup)

jayhawker

04-23-2007, 11:35 AM

Jayhawker now has a separate page where you can read more information and download the utility... www.tsptalk.com/tsplookup (http://www.tsptalk.com/tsplookup)

Thanks Tom!

Okay! I'm done with the update and it will be available for download this evening. When you click on the download link and see the filename "TSPLookup2.zip" you'll know you're getting the latest and greatest version.

Some fixes were made in the Dashboard. The code/logic for this form is complicated and I didn't quite have it correct. The changes are:

1) When entering share values in the Dashboard, if the user entered something other than a number or a decimal point (i.e.; a letter or two decimal points), execution of the program would halt with an error on the tab change event.

Resolution: Trapped this error. The user can now only enter numbers - with or without a single decimal point.

2) If, in the evening after the TSP share values are current, the user entered their share values for tomorrow, these values would overwrite the previous days values when the spreadsheet was opened the next day.

Resolution: Now, the user cannot update their share values after the share prices are current and the tab reads "Tomorrow." Also, now the user cannot update their share values on Sunday.

3) When the user opened the spreadsheet in the morning before the US markets have opened for the day, the tabs "Today's Estimate" and "TSP Fund Quotes" would show the previous day's closing results for the F, C and S funds temporarily skewing the totals (just) until the US markets opened. Not a big deal and I'm sure most users understood the data they were seeing was from the day before, but I tried to fix it anyway.

Resolution: Assuming the date/time data from Yahoo is correct and in the correct format (and that's a big IF with Yahoo lately) the previous day's closing results will no longer appear when viewing these two tabs. (I might have to revisit this if it doesn't work correctly consistently.)

4) The links to PayPal and my email address didn't work in the "About" form.

Resolution: D'OH! Is this why YOU haven't sent any money??? C'mon users... you've got tens of thousands of $$$ invested. $10 is pocket money, chump change, a decent tip. Send money! :)

That's all folks! Enjoy!

Ed.

Frixxxx

04-23-2007, 03:53 PM

Your post on 350z's thread

EAFE final: -.215 or -5 cents.
is NOT:confused: the TSPlookup "I fund" results I am seeing on the spreadsheet.

What am I missing?

Nevermind....estimate....it's estimating

jayhawker

04-23-2007, 07:04 PM

Your post on 350z's thread

is NOT:confused: the TSPlookup "I fund" results I am seeing on the spreadsheet.

What am I missing?

Nevermind....estimate....it's estimating

I'll count this as the "Infrequently Asked Question of the Day!"

Honestly, I posted the EAFE quote from bloomberg.com instead of from the spreadsheet in 350z's thread today because the Yahoo quote for FTSE was wrong. The Yahoo quotes are going to be wrong on occasion. It's annoying as all get out but I can't control it. I have complained to Yahoo though.

Thanks for your question. Ed.

jayhawker

04-24-2007, 11:03 AM

Infrequently Asked Question of the Day

#5 - Can you add the L-Funds to the spreadsheet and/or the Dashboard?

Nope. No can do. Because...

The tsp.gov website uses flash control to display the percentages. (You know - those fancy pie charts. They must have hired that work out because that presentation is sooo different from the rest of the website!) If the L-Fund distribution values were to be found in an existing table or database that I could access I might have given it a shot. I thought about gathering the data by looking at the pie charts... for each fund... for each three month period... for the next... how many years??? Yes, I thought about that for all of about five seconds! :nuts:

You'll just have to break down your L-Fund distribution yourself.

Thanks for your question. Ed.

jayhawker

04-24-2007, 02:04 PM

Your post on 350z's thread

is NOT:confused: the TSPlookup "I fund" results I am seeing on the spreadsheet.

What am I missing?

Nevermind....estimate....it's estimating

Better today - yes? TSPLookup had a Final Estimate of .-350 at 11:18 CDT. The actual EAFE final was -.366. That's close enough for government work! :)

jayhawker

04-25-2007, 11:23 AM

Infrequently Asked Question of the Day

#6 - What formula(s) are you using to project the account balance?

Excel has a worksheet function named Future Value (FV). It works like this:

________________________________

FV - Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Syntax - FV(rate,nper,pmt,pv,type)

Rate is the interest rate per period.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.

Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
________________________________

That burned a few brain cells!

Thanks for your question. Ed.

jayhawker

04-26-2007, 12:04 PM

Infrequently Asked Question of the Day

#7 - When I rename the file or save it as a different filename other than TSPLookup2.xls, the Dashboard doesn't work. Why is that?

It's related to the fact that the Dashboard is a non-modal form and can be on top of any application. When you are entering your share prices in the Dashboard, the "focus" must be on TSPLookup2.xls.

I tried numerous methods - the most obvious one being the Excel VBA property "ThisWorkbook", but the only way I could successfully get the focus of/on the workbook when the Dashboard was not on top of the workbook was to activate the workbook by its unique filename - like this:

If ActiveWorkbook.Name <> ("TSPLookup2.xls") Then
Application.ScreenUpdating = False
Application.Windows("TSPLookup2.xls").Activate
End If

If the file is not named TSPLookup2.xls then it cannot be activated. That's why you can't rename it. Also, even though I turned off the ScreenUpdating, you'll still see a "blink" when you are entering numbers when the Dashboard is on top of anything other than the workbook. The blink happens because the program is (very quickly) switching back and forth between applications.

Fascinating stuff - huh?

Thanks for your question. Ed.

ayla

04-26-2007, 01:44 PM

By the way, your Excel skills are excellent. It is a masterpiece. I should probably do more research about using macros. I could probably cut down my file sizes considerably by using a macro instead of using the same statements over 14000 times!

fabijo - wish you WOULD write this app in C++. Would be nice to get rid of the Excel requirement all together. I've got Excel 2000 (which tsplookup doesn't support) and plan to soon convert to the Opensource spreadsheet (named Calc I think? or whatever they call it) and eliminate Excel all together.

Anyway, anytime we can eliminate Microsoft in the path, that's a GOOD thing IMO. Let me know if you ever make a stab at it. I might do it some day but too many projects going on now. By the time I get 'round to it, my old age dementia problems will have taken over, oh well.

jayhawker

04-26-2007, 02:56 PM

fabijo - wish you WOULD write this app in C++. Would be nice to get rid of the Excel requirement all together. I've got Excel 2000 (which tsplookup doesn't support) and plan to soon convert to the Opensource spreadsheet (named Calc I think? or whatever they call it) and eliminate Excel all together.

Anyway, anytime we can eliminate Microsoft in the path, that's a GOOD thing IMO. Let me know if you ever make a stab at it. I might do it some day but too many projects going on now. By the time I get 'round to it, my old age dementia problems will have taken over, oh well.

We're you directing this to me?

Try using TSPLookup in Excel 2000. It has been reported that it will work in Excel 2000 on a PC running XP. There's nothing in TSPLookup that shouldn't work in 2000. It should work. But I tested it on a box running Win98 and it didn't work. You'll just have to try it to know if it will work or not.

I don't know C++. I know some Fortran, VMS, and linux/unix and that's about it. Secondly, if the program was in C++ and compiled into an executable, most government employees would not be able to run it on their work PC - or email it for that matter.

Can't please everyone! :)

Ed.

jayhawker

04-26-2007, 03:02 PM

EAFE final of -.139 today. TSPLookup had that EXACT number at the close (12:15 EDT.) Hit it right on the nose! Amazing!!! :)

jayhawker

04-27-2007, 11:24 AM

Infrequently Asked Question of the Day

#8 – I noticed you updated the file yesterday. I downloaded it but I don’t see any changes. What changed?

Not much.

1) I added two hyperlinks down in the disclaimer; one to the TSPLookup webpage here at TSPTalk and one to the place where I bought the icons (the link is part of the licensing agreement.)

2) In the About form, I added a link to the webpage here.

3) Reworked the code for the way it was determining if the USM hadn’t opened yet. I thought I might have to revisit this - and I did.

4) I cleaned up some code. What can I say – I’m a perfectionist. :o

The basics are the same. Get the updated version at your leisure.

Thanks for your question. Ed.

04-30-2007, 06:11 AM

How do I download file? Thanks for your input.

Ed

jayhawker

04-30-2007, 09:12 AM

How do I download file? Thanks for your input.

Ed

Simply click on the link in my signature. :)

RPM

05-03-2007, 02:55 AM

jayhawker

05-03-2007, 08:11 AM

Jayhawker:

Does your I fund estimate account for the currency exchange? I run both your spreadsheet and ChemEng 's spreadsheet, and while your EAFE is right, your I fund estimate doesn't seem to account for the dollar exchange that you have in the bottom right hand side box.

Thanks,

Yes, TSPLookup takes into account the movement in the foreign currencies vs. the US$. However, it doesn't use the DX$ singularly. Instead, it uses each country's individual currency (even though not all of them are shown on the spreadsheet.)

That lower-right box is for YOUR use. That box is pre-populated with a few symbols I like to track but you may replace those symbols with any that you want to track. The DX-Y.NYB symbol and data there in that box are not used by TSPLookup in the I Fund estimate calculation.

Thanks for your question. Ed.

RPM

05-04-2007, 04:39 AM

jayhawker

05-04-2007, 08:38 AM

I have a suggestion for the spreadsheet: next to the name of each OSM, please put the percentage for each fund. Your spreadsheet looks so nice, and that's the only place I could think of to make the suggestion without changing it.

Thanks,

Thanks for the suggestion. Let me think on it.

The countries are listed in order of highest percentage to lowest.

Country composition of the I Fund (http://tsp.gov/rates/fundsheet-ifund.pdf)

Thanks, Ed.

jayhawker

05-09-2007, 01:11 PM

Infrequently Asked Question of the Day

#9 - Why haven't you posted the Infrequently Asked Question of the Day lately???

No one has asked any new questions!

Bump! :D

jayhawker

05-10-2007, 06:49 PM

TSPLookup users:

I updated the file today.

I found a problem with the MSCI EAFE change value. This value is in the upper right-hand corner in cell P7. The value would be correct if the user updated in the evening after the final MSCI EAFE was available for retrieval; it would be incorrect if the user updated and retrieved it the next day. I have changed the way I was calculating this value and the problem has been corrected.

Nobody tipped me off to this. I found it myself. :o

Download the updated version at your convenience.

Thanks for using TSPLookup!

Ed.

jayhawker

05-17-2007, 04:00 PM

Infrequently Asked Question of the Day

#10 – In reference to the bug fix above… how can a cell formula be correct one day and not the next?

There is no formula in that cell – or any of the cells for that matter. I’m making all the calculations in code and then I use code to place the values in the appropriate cells. I did this because it made the file size significantly smaller. I had a working version that did have cell formulas and it was over 2 megs in size. It was slow to load and save. I thought it was unwieldly and inconvenient. ChemEng suggested to me that I could do it all in code - and he was right.

The problem above was the result of bad thinking. Originally, I was subtracting the current result from the previous result. But, come to realize, this only worked if the previous result was the previous DAY'S result. And depending on when the spreadsheet was last updated/used, that wasn't necessarily the case. My beta-testers and I didn't see the error because all of us were using the spreadsheet EVERY DAY and the error never occurred!

Thanks for your question. Ed.

jayhawker

06-07-2007, 05:55 PM

TSPLookup users:

I updated the file today. The only change involves retrieval of the share prices from tsp.gov.

At times, the webmaster of tsp.gov will insert an informational banner on the share price page. This usually happens a few days before a holiday - reminding tsp participants that share prices won't be updated on the upcoming holiday. When in effect, this changes the location of the share price table that TSPLookup retrieves. While I planned ahead for this and TSPLookup handled it okay this past holiday, it wasn't quite perfect. So, I fixed it. :)

There's no rush. Download the updated version at your convenience.

Thanks for using TSPLookup!

Ed.

jayhawker

06-20-2007, 01:16 PM

Infrequently Asked Question of the Day

#11 - I was wondering if you had a version of TSPLookup that was not password protected? I ask because I would like for it to run on my PDA but the Mobile version of Excel does not open spreadsheets that are password protected.

The short answer is - no - I don't support a version for PDAs. However, if there was a demand for one I would consider creating one. You're the first person to ask for it however.

Thanks for your question. Ed.

charmed855

06-20-2007, 01:52 PM

BTW, have you tested it with OpenOffice Calc? I run that at home and have assumed that it is not supported, but thought I would ask.

oreo

06-20-2007, 02:02 PM

Jayhawker,

I don't have paypal but would like to contribute. Is there any way to make a contribution without paypal?

Thanks,

Oreo

jayhawker

06-20-2007, 02:02 PM

I haven't tested it but OpenOffice Calc does support Microsoft Excel spreadsheets. It should work just fine. Try it at home and let me know.

Ed.

jayhawker

06-20-2007, 02:04 PM

Jayhawker,

I don't have paypal but would like to contribute. Is there any way to make a contribution without paypal?

Thanks,

Oreo

Sure! I'll PM you my mailing address. Thanks!

tspgo_com

06-20-2007, 02:51 PM

I haven't tested it but OpenOffice Calc does support Microsoft Excel spreadsheets. It should work just fine. Try it at home and let me know.

Ed.

I keep calculating the early estimate manually because I do not have MS Excell at work. I can open the file with OpenOffice Calc but it does not update.

Thanks.

jayhawker

06-20-2007, 02:56 PM

I can open the file with OpenOffice Calc but it does not update. Thanks.

I'll see if I can modify it so it will work in OOc. Ed.

jayhawker

06-20-2007, 07:42 PM

I'll see if I can modify it so it will work in OOc. Ed.

Uh... hmmm... I don't think so! :blink:

It would require a complete rewrite in a macro language I'm not versed in. I don't have the time or will to devote to it. Honestly, I haven't received enough donations to justify continuing to maintain and update the existing Windows version. :(

Ed.

buda

07-13-2007, 06:34 PM

this won't work with Quatro will it?

jayhawker

07-21-2007, 04:55 PM

this won't work with Quatro will it?

Corel Quattro? No, it won't.

(Sorry I'm so late responding. I didn't see the thread get bumped up and hadn't checked it.)

Ed.

buda

07-23-2007, 11:16 PM

Ok . Am I missing something...for some reason I cant enter into the dashboard...

any suggestions.

buda

07-24-2007, 01:35 AM

nevermind i figured it out.

jayhawker

07-24-2007, 08:09 AM

nevermind i figured it out.

Cool! :)

buda

07-25-2007, 10:28 PM

Cool! :)

once again I am not able to edit shares in the dash board. I was able to do it once then it zeroed out and will not let me input...

any suggestions?

buds

jayhawker

07-26-2007, 09:13 AM

once again I am not able to edit shares in the dash board. I was able to do it once then it zeroed out and will not let me input...

any suggestions?

buds

Read post #25 in this thread. Those cells are disabled after the share prices are updated in the evening and on Sundays.

Ed.

tsptalk

08-21-2007, 05:10 PM

jayhawker

08-21-2007, 06:11 PM

Ed -
Have you thought about updating the F fund quote by using the bond yield quotes on Yahoo, instead of the AGG? 350z has been doing this and the estimates have been better than the AGG lately. I'm not sure why the AGG has been so off.

Just a suggestion.

Thanks!
Tom

I'm not an expert on bonds. But I think investors are buying and selling short term notes because they're jumping in and out of stocks. They can't decide whether to be in or out. This volatility in the short term notes and the disparity of their prices as compared to the long term notes may be throwing off the AGG.

The AGG has been a fairly reliable measure in more stable times and the value is easy for me to retrieve. But, I'll watch the $TNX for awhile and see if 350Z's method is significantly more accurate over time.

Thanks for the suggestion. :)

Ed.

jayhawker

08-29-2007, 09:07 PM

I have updated TSPLookup to v2.1! These are the changes:

1) Now using 350zCommTech’s method to calculate the F Fund share price change. (I’m still showing the quote for the AGG however. It just appears more logical than showing a negative yield for the TNX and TYX and then showing a positive share price change.)

2) Now using the Bloomberg quote (EFD:IND) for the I Fund.

3) Eliminated the need to save the I Fund estimate at 12:15 P.M. ET.

4) Eliminated the need to select your local time zone (and eliminated that userform.)

5) Re-ordered the countries that comprised the I Fund in the new order of highest percentage to lowest.

6) Re-ordered the foreign currencies in order of highest percentage to lower.

7) Added the percentage composition to the I Fund country column (as suggested/requested by RPM.)

8) Added support for 1280 x 800 screen resolution (for those of you with widescreen LCDs and laptops.)

9) Made a few minor code changes to improve performance.

That’s it. Lots of changes!!!

I haven’t changed the screenshots on www.tsptalk.com/tsplookup (http://www.tsptalk.com/tsplookup) yet but I’ll get around to it soon. Download the file at your convenience (click the link in my signature.)

Thanks for using TSPLookup!

Ed.

Eldritch Flatus

08-31-2007, 01:37 AM

Hi Jayhawker; I use and love your spreadsheet, great work! I just downloaded the lastest version, and is it just me, or is there a glitch?
All the price and "my money" information fields have reset to 0, giving me a balance of 0......... scary! Heh. This is true for both yesterdays and todays estimate tabs.. Hafta try and retreive the old version. ( Now where in the heck did I put that file) .... hmmmm ... Heh:blink:

Eldritch Flatus

08-31-2007, 02:01 AM

jayhawker

08-31-2007, 07:59 AM

Erk!... I mean after I hit Update on the dashsboard, the Price information fields are 0 for both the Yesterday's (8/30/2007), and the Today's Estimate tabs, resulting in 0 for the amounts in the fund balances and the Total fields of the dashboard. [sorry about that sentence].

That could be normal behavior for the Todays estimate this time of day but not, I think for yesterdays.

Gadit'slate! Igotdagotabed.:o

It's broke! OH NO! :embarrest: I guess I streamlined the code a wee bit too much!

Okay... the share prices are missing. This happened because the TSP website added an informational banner on that page informing you of the Labor Day holiday AND I TOOK OUT THE CODE THAT ADJUSTS FOR THAT! (Smacks forehead!!) I'M AN IDIOT! :)

Sorry! I'll fix it tonight, upload the file and let you know here. In the meantime, look at the pretty green numbers on the board today!!! :D

Ed.

Eldritch Flatus

08-31-2007, 08:55 AM

You are not an idiot! Lookup is a beautiful piece of work! Smaks of genius! Green has always been my favorite color. Any color green is better than any other color. (Obvious exceptions not withstanding.. hmmm..not too sure of the accuracy of that syntax):D

jayhawker

08-31-2007, 03:54 PM

I fixed it! Download the updated file at your convenience.

I apologize for my error and your inconvenience.

Ed.

USPS1980

08-31-2007, 07:45 PM

First post here---GREAT website. Very professional and informative.

Jayhawker----FANTASTIC tool man! Your Excel skills are first class...kudo's.

I've been using TSPLookup for a few months now...I have a confession to make. *standing up* "Hi...I'm USPS1980 and I am a buy and holder" *hears the gasps from the crowd*

Yes, I am a buy and holder---but, that's changing! My old strategy was simple---as I said above---buy and hold. I'd sit in the S fund and ride the roller coaster day in, day out. Now, I did decent enough by doing as such...the S fund gives good returns over the long haul. BUT, since using TSPLookup as a daily "guide" I've made several balance transfers since early June and my profits are SO much nicer than they would have been by just riding the aforementioned S coaster...

Thanks Ed for the work...its greatly appreciated! I hope you're supported by the users here.

jayhawker

08-31-2007, 08:02 PM

Thanks for the kind words I (I needed them today!) and your generous donation (I got it!)

Welcome to the board!

Show-me

08-31-2007, 09:01 PM

Fellow USPSer, welcome and glad to see ya posting on the MB.

Jayhawker,

Hang in there on the job. I'm pull'n for ya. Enjoy your time in Norman.

First post here---GREAT website. Very professional and informative.

Jayhawker----FANTASTIC tool man! Your Excel skills are first class...kudo's.

I've been using TSPLookup for a few months now...I have a confession to make. *standing up* "Hi...I'm USPS1980 and I am a buy and holder" *hears the gasps from the crowd*

Yes, I am a buy and holder---but, that's changing! My old strategy was simple---as I said above---buy and hold. I'd sit in the S fund and ride the roller coaster day in, day out. Now, I did decent enough by doing as such...the S fund gives good returns over the long haul. BUT, since using TSPLookup as a daily "guide" I've made several balance transfers since early June and my profits are SO much nicer than they would have been by just riding the aforementioned S coaster...

Thanks Ed for the work...its greatly appreciated! I hope you're supported by the users here.

jayhawker

09-24-2007, 04:33 PM

2) Now using the Bloomberg quote (EFD:IND) for the I Fund.

It seems bloomberg.com changed the layout of the EFD:IND quote. This change caused the I Fund quote in TSPLookup to be "messed up."

If it's still this way tomorrow, I'll assume the change is permanent. I'll adjust TSPLookup, upload the updated file, and let you know here.

Ed.

jayhawker

09-25-2007, 04:20 PM

jayhawker

10-27-2007, 05:04 PM

It seems bloomberg.com changed the layout of the EFD:IND quote. This change caused the I Fund quote in TSPLookup to be "messed up."

If it's still this way tomorrow, I'll assume the change is permanent. I'll adjust TSPLookup, upload the updated file, and let you know here.

Ed.

Once again, bloomberg.com has changed the layout of the EFD:IND quote. :suspicious: This change is causing the time of the quote to be "messed up."

This is an annoyance but only the time of the quote is affected. Tsplookup is still showing the value of the quote correctly - which is the most important part.

I'll look at this the first part of next week, fix the code and add some error checking to ensure tsplookup will show the time of the quote correctly.

Ed.

jayhawker

10-31-2007, 04:05 PM

I updated TSPLookup2 today and it is now available for you to download. Click the link in my signature.

Thanks for using TSPLookup!

Ed.

buda

11-05-2007, 09:22 PM

will this work / update with MS Excell Reader.?

jayhawker

12-12-2007, 09:06 PM

will this work / update with MS Excell Reader.?

Nope!

jayhawker

12-12-2007, 09:14 PM

TSPLookup users:

There appears to be a problem with the web queries from Yahoo.com tonight. If this problem doesn't fix itself in a day or two, I'll update TSPLookup for you.

Also, Yahoo states the symbol ^FCHI (France's CAC 40) is no longer valid. That's absurd! What idiot is in charge at Yahoo finance???

Ed.

wv-girl

12-13-2007, 04:26 AM

jayhawker

12-13-2007, 11:50 AM

Thanks wv_girl! Hey, have YOU tried TSPLookup yet? :)

Okay, I've modified TSPLookup to work with the changes Yahoo has made. I'll upload the file when I get home today and you can download it from the link in my signature.

For all those that have donated, I'll send you an email message letting you know the file has been updated and is ready for download.

Ed.

jayhawker

12-17-2007, 07:46 AM

TSPLookup users:

Looks like we're having trouble getting the EFD quote from bloomberg.com this morning. The quote is still here (http://www.bloomberg.com/apps/quote?ticker=EFD:IND) - in the same place as usual - but we're not successfully getting to that page every time. I suspect bloomberg is tweaking their website this morning. Not to worry. It'll fix itself. Just keep updating and eventually you'll get the quote.

Ed.

jayhawker

01-22-2008, 04:08 PM

TSPLookup users:

...Yahoo states the symbol ^FCHI (France's CAC 40) is no longer valid. That's absurd! What idiot is in charge at Yahoo finance???

Ed.

It was changed to FCE.NX without the little ^ :)

Yahoo has changed the France symbol back to ^FCHI! :blink:

I have updated TSPLookup.

The ^STI quote for Singapore is not available at this time. From Wikipedia:

"The Singapore Exchange, London's FTSE Group, and Singapore Press Holdings announced on 6 June (http://en.wikipedia.org/wiki/June_6) 2007 (http://en.wikipedia.org/wiki/2007) that the Straits Times Index will be set for a major overhaul by the end of 2007. The number of component stocks will be reduced from 50 to 30 and at the same time, 18 new indices will be created. It is known that the new STI indices would be published on the FTSE website beginning September. The announcement stated that the revamped STI and the current STI will be displayed together then, until the switchover in December.

On 10 January 2008, the new indices became active, albeit to much confusion across the market. The introduction of such indices has been poorly implemented in the indices themselves are no longer displayed on the SGX web site, and they are no longer supplied on their live data feeds to brokers."

http://en.wikipedia.org/wiki/Straits_Times_Index

Thanks for using TSPLookup!

Ed.

jayhawker

02-14-2008, 08:22 PM

An updated version of TSPLookup is available for download.

I fixed the S Fund quote from Yahoo and the currency quotes from ino.

Ed.

jdubya

02-15-2008, 08:37 PM

At times, the webmaster of tsp.gov will insert an informational banner on the share price page. This usually happens a few days before a holiday - reminding tsp participants that share prices won't be updated on the upcoming holiday. When in effect, this changes the location of the share price table that TSPLookup retrieves. While I planned ahead for this and TSPLookup handled it okay this past holiday, it wasn't quite perfect. So, I fixed it. :)

Ed,

If I may ask, how do you account for this in your coding when TSPLookup retrieves the share prices?

jayhawker

02-16-2008, 02:35 PM

Ed,

If I may ask, how do you account for this in your coding when TSPLookup retrieves the share prices?

I use two web queries that places the data in two different places on a data sheet. The first query retrieves the share price table its from usual location, and then I check to see if the data is actually retrieved. If not, then I run another web query the retrieves the next table below. In a third location, I simply check which has valid values. To place data in the main page and the userform, I use the data from this third location.

Ed.

jdubya

02-18-2008, 07:08 AM

Thanks for the reply, Ed.

You have a great program.

jayhawker

03-14-2008, 03:32 PM

I fixed a problem with the S Fund quote.

Click on the link in my signature to download the updated file.

Thanks,

Ed.

jayhawker

04-08-2008, 06:39 PM

I fixed a problem with the I Fund results from mscibarra.com.

Click on the link in my signature to download the updated file.

Thanks,

Ed.

Fivetears

05-21-2008, 06:28 PM

Anyone still have a working copy of this? Perhaps something new?
I started getting 1004 Run-time errors a couple days ago.

The copy below has the same problem:
http://workingmansblog.com/TSP/I_fund_lookup.xls
This one two:
http://www.tsptalk.com/mb/showpost.php?p=161350&postcount=35
I think they're related. :nuts:

luv2read

05-21-2008, 08:43 PM

Fivetears

05-21-2008, 09:28 PM

I'm getting program script 1004 Run-time errors L2R. I normally ran it after 7pm to cut and paste into my Excel market graphing spreadsheet. I guess I'm back to the "horse and carriage" key punching activity. It was very good while it lasted CE, if it can't be fixed.
Thanks. :)
See this post. ChemEng is checking on it.
http://www.tsptalk.com/mb/showpost.php?p=165250&postcount=157

jayhawker

06-02-2008, 06:20 PM

I updated TSPLookup today. I updated the country composition percentages of the I Fund and reordered them from highest to lowest. No, they haven't changed recently... I just happened to notice. Sorry, I've been busy! :o

Click on the link in my signature to download the updated file.

Thanks,

Ed.

nnuut

06-02-2008, 08:50 PM

jayhawker

06-02-2008, 09:36 PM

By the way, Jayhawker we really appreciate you keeping this valuable tool updated for our use. You are one of many that make this a GREAT place to learn and beat the system, it's all about MAKING MONEY and you help a lot!!
Thanks
Norman:D

Norman, thanks for the kind words.

I haven't been around here be much because I truly have been busy. I'm working for HQ now so I've got multiple projects in the works and I'm traveling a lot. But I do try to catch up with the non-premium posts once a week.

Ed.

jayhawker

06-04-2008, 05:03 PM

I updated TSPLookup again today. I fixed an error in postulating the FV amount (I Fund Fair Valuation.) I suppose this has been broken since about 4/8/08 when mscibarra.com changed their table. I made an update at that time for that table change but didn't notice that the FV calculation was also affected. Anyway, the FV should be correct again.

Click on the link in my signature to download the updated file.

Thanks,

Ed.

jayhawker

07-16-2008, 07:17 PM

I updated TSPLookup today. The share prices are now shown carried out to four decimal places.

Click on the link on my signature to download the updated file.

Thanks,

Ed.

tsptalk

07-16-2008, 07:18 PM

Still a great utility! Sending another donation. Who's with me?

jayhawker

08-04-2008, 09:02 PM

I updated TSPLookup today.

An error in getting the EFD quote from bloomberg.com has been fixed.

Ed.

luv2read

08-07-2008, 06:25 AM

Hi,

First I want to say how valuable tsplookup and Ifund lookup are and thank you for providing them, and keeping them in working order! :D

I downloaded the updated TSPLOOKUP yesterday. I ran it between 11-12, then again at the end of the day. I ran it again this morning - of course this early there are no F, C, S results showing, but there are results for I Fund. It is in the RED. I still had the earlier version on my desktop and had accidentally clicked that one first, and it is in the GREEN. I also use your I fund tracker. All three give wildly differing results when run. I fund results at 6:08 this morning:

pre-8/4 (6/4 version i think) tsplookup version today's estimate = 21.54 up .32
8/4 tsplookup updated version today's estimate = 20.8374 down -0.3806
I fund lookup = 21.27 (6:08 estimate: +1 cent from market change PLUS +6 cents from dollar change EQUALS +6 cents)

I don't know why the three spreadsheets are calculating this differently. If it's not too much trouble could you please verify which is correct? TIA!:D

jayhawker

08-24-2008, 12:21 PM

Thanks for your question. Sorry for my tardy response.

After the fact, I can't say why there may have been a discrepency in the two versions of TSPLookup. ("I Fund Lookup" is ChemEng's spreadsheet.
I don't have anything to do with it.)

Looking at past results, the I Fund was down about 32 cents on 8/6, so I'd have to conclude that the current version of TSPLookup was the most correct.

TSPLookup gets actual quotes; the F, C, and S from yahoo and the I from bloomberg. Rarely, these quotes are wrong at the source.

Ed.

jayhawker

01-26-2009, 07:05 PM

Hi folks!

Today, TSPLookup is not able to retrieve the share prices from tsp.gov. The problem is because the tsp.gov website is redirecting direct access of the share price page to the home page.

You can see this for yourself by trying to go directly to the share price page yourself - http://www.tsp.gov/rates/share-prices.html . The link instead takes you to the home page.

The web query in TSPLookup won't work because of this redirection.

Let us hope that they fix/change this.

Ed.

tsptalk

01-26-2009, 07:49 PM

Hi JH -

Are you sure that is the problem? They've been doing that redirect for years now. I know because I think I was reason. I used to link to their share price page and they did not like that at all, so they started to redirect to the home page.

One of our members at the time, wrote me a javascript to get by it. A few weeks later I got a certified letter from the TSP telling me to stop. They said people would think the data was on TSP Talk and not TSP.gov.

jayhawker

01-26-2009, 08:39 PM

Hi Tom,

Yes, I seem to remember reading about that here on the site. This hasn't been a problem for TSPLookup all this time, but it does seem to be the problem now.

Trying to fix TSPLookup, I'm able to select the table of share prices from the share page but it doesn't stick. It returns a selection from the home page instead.

There's an option when setting up a web query to "ignore redirection" but it's not having that effect. I'm stumped at the moment.

Ed.

tsptalk

01-26-2009, 11:46 PM

Can you read the TSP Talk home page, or the autotracker price page? I usually have them updated by 7:30-8 PM ET. Occasionally I am out and it's later.

jayhawker

01-27-2009, 06:19 PM

The autotracker price page works for me! Thanks Tom. It was a simple change.

I will check the tsp.gov share price page occasionally to see if they change back to the status quo.

-----------------------------------------------------------------------

TSPLookup has been updated today and is available for download. Click the link in my signature below.

Thanks for using TSPLookup!

Ed.

tsptalk

01-27-2009, 06:44 PM

Cool! Today's prices are now available in the autotracker if you need to test...

jayhawker

01-27-2009, 07:14 PM

Cool! Today's prices are now available in the autotracker if you need to test...

I did. Works great, lasts a long time! :)

Edit: I just checked and the web query from tsp.gov is working again! Sheesh! I'll wait a day or two and if it stays the same I'll switch back to the previous method.

Ed.

luv2read

01-28-2009, 06:45 AM

TSP Lookup and I fund lookup versions prior to yesterday are now working fine again....

TSP BOARD ....isn't it enough you screwed witht the IFT's...leave our TOOLS alone!!!!!:mad:

jayhawker

02-11-2009, 07:44 PM

I updated TSPLookup today. Set it back to getting the TSP Share prices from tsp.gov.

Please download the latest version. Click the link in my signature below.

Thanks!

Ed.

jayhawker

04-20-2009, 01:02 PM

I have updated TSPLookup. I fixed a problem with getting the EFD quote from bloomberg.com.

Please download the updated version from the link in my signature.

Thanks,

Ed.

jayhawker

07-28-2009, 05:00 PM

I have updated TSPLookup today. I fixed a problem - * again * - with getting the EFD quote from bloomberg.com.

Seems bloomberg.com moves the values I'm looking for every once in awhile. With this update, I'm now attempting to check both places the values have been recently.

Please download the updated version from the link in my signature.

Thanks,

Ed.

tsptalk

07-28-2009, 05:33 PM

Thanks jayhawker!

jayhawker

07-28-2009, 08:29 PM

Thanks jayhawker!

Thank you Tom for your continued support! :)

jayhawker

07-29-2009, 05:26 PM

Got a major problem! The F, C, S, and foreign quotes on the left side of the application are not updating. Neither are your choice of "other" quotes in the lower right. Basically, none of the quotes from Yahoo are updating. Because...

Yahoo has changed their html code so that results on multi-quote pages are no longer in tables. You'll see they've added this blurb:

"Tell us what you think about the redesigned multi-quotes page."

I don't think much of it! :mad: Nope... I don't like it one bit!!!

About the only thing that is working is the I Fund quote from bloomberg! How ironic! The foreign currency quotes still work also.

I need the data in tables because I'm using web queries to return the data. Having to go someplace else to get the quotes is going to cause me some re-design. I'll work on it but I can't promise anything at this point.

I've disabled the download link.

Ed.

tsptalk

07-29-2009, 06:42 PM

Bummer... None of them work?

http://finance.yahoo.com/q?s=%5EGSPC,VXF,EFA,AGG&d=s

http://finance.yahoo.com/q/cq?s=%5EGSPC,VXF,EFA,AGG&d=v1

http://finance.yahoo.com/q/cq?s=%5EGSPC,VXF,EFA,AGG&d=e

http://finance.yahoo.com/q/cq?s=%5EGSPC,VXF,EFA,AGG&d=v2

http://finance.yahoo.com/q/cq?s=%5EGSPC,VXF,EFA,AGG&d=v3

jayhawker

07-29-2009, 07:07 PM

Nope. Sooo aggravating!

uscs344

07-30-2009, 02:31 PM

I just realized how much I liked the tsplookup now that I don't have it. Good luck on getting it fixed. What a great tool.

jayhawker

07-30-2009, 06:02 PM

Success!!! WooHoo!!!

I fixed it. I'm still getting most of the quotes from Yahoo, just had to come up with a new and improved method. Hey, no prob! :D

There is a small change in the way it works. Previously, when you opened the application the first time on a new day, only the F C S I and the TSP share prices updated. Now, ALL the quotes and TSP share prices will update on the auto-open.

It's now v.2.5

Please download the updated version from the link in my signature.

It only got one day's testing so if you discover any problems, please PM or email me.

Thanks to all for using and supporting TSPLookup! (Go ahead, send me a couple of bucks. It's worth it! :rolleyes: )

Ed.

PessOptimist

07-30-2009, 08:31 PM

Thanks JH. And here I was blaming those evil IT types at work:suspicious:

jayhawker

07-30-2009, 08:56 PM

I just realized how much I liked the tsplookup now that I don't have it. Good luck on getting it fixed. What a great tool.

Thanks! Happy that you use it and like it.

Thanks JH. And here I was blaming those evil IT types at work:suspicious:

Yeah, I know what you mean. Various Yahoo quotes in the "other" section in the bottom right don't come through on the Postal network. They work fine at home but not at work. It's definitely a network/firewall thing. :blink:

uscs344

07-30-2009, 10:22 PM

Jayhawker I tried to download the new version but I got a message saying the link is broken.

HTTP 404 - File not found
http://www.google.com/intl/en/images/logos/toolbar_logo.gif (http://toolbar.google.com/) Oops! This link appears to be broken.

Suggestions:

Go to www.tsptalk.com (http://www.tsptalk.com/)
Go to www.tsptalk.com/tsplookup (http://www.tsptalk.com/tsplookup/)
Search www.tsptalk.com for tsp lookup TSP Lookup 25 (http://www.google.com/search?q=tsp+lookup+TSP+Lookup+25+site:www.tsptalk .com)
Search on Google:

Google Toolbar Help (http://www.google.com/support/toolbar/?hl=en) - Why am I seeing this page (http://www.google.com/support/toolbar/bin/answer.py?answer=75816&hl=en)

KevinD

07-31-2009, 05:53 AM

Me too...

jayhawker

07-31-2009, 08:16 AM

Jayhawker I tried to download the new version but I got a message saying the link is broken.

HTTP 404 - File not found
http://www.google.com/intl/en/images/logos/toolbar_logo.gif (http://toolbar.google.com/) Oops! This link appears to be broken.

Suggestions:

Go to www.tsptalk.com (http://www.tsptalk.com/)
Go to www.tsptalk.com/tsplookup (http://www.tsptalk.com/tsplookup/)
Search www.tsptalk.com (http://www.tsptalk.com) for tsp lookup TSP Lookup 25 (http://www.google.com/search?q=tsp+lookup+TSP+Lookup+25+site:www.tsptalk .com)
Search on Google:
Google Toolbar Help (http://www.google.com/support/toolbar/?hl=en) - Why am I seeing this page (http://www.google.com/support/toolbar/bin/answer.py?answer=75816&hl=en)

Me too...

Darnit! I messed that up! I'll fix it later this afternoon. Sorry.

Ed.

tsptalk

07-31-2009, 08:57 AM

I think see the problem (or a problem) . I renamed the zip file.

It will download now, but I am not sure if it (the zip file) on the server is the correct version. I am seeing quotes for the F and I funds, but not C and S.

jayhawker

07-31-2009, 04:50 PM

Thanks Tom. Yes, the zipfile was named incorrectly.

But that version was not right after all. Needed a bit more testing than I made time for. Figures.

I made a couple more updates:

1) updated the foreign percentages and re-ordered them
2) changed one symbol (The Netherlands)
3) re-ordered the currencies
4) modified the F Fund estimate calculation

I think it's good to go now. :)

Ed.

jpr

08-07-2009, 09:17 AM

Is there a comprehensive guide to following this spreadsheet?

I seem to have problems understanding a couple things. For example, the TSP Fund sections: C and S always show "false" under time. The countries sections shows false down the entire list except for Denmark. TSP Shares show empty for F, C, and S. Under the Other section, I wanted to enter the EDF ticker that you have for I in the TSP Fund section. It comes up with something completely different.

I like the dashboard very much. I'm not sure if I am doing something wrong, but the only way to update the TSP Lookup sheet is to close the dashboard. No problem there, but when I want to reopen the dashboard I have to reenter all of the values again. Is there a way to keep the values stored? On the 3rd tab, TSP Fund quotes, I'm guessing this is what they are at the time I update it. C and S have zeros across the row. The I fund values do not match that of the TSP Lookup spreadsheet. What am I reading wrong?

I am using version 2.5.

Thanks.

jayhawker

08-13-2009, 09:58 PM

jpr (and all TSPLookup users),

I have a some new solutions, will test them this weekend and Monday, and hope to post another update on Tuesday.

The changes I made recently don't play well behind our government firewalls. jpr, this is why you're seeing FALSE where there should be values. TSPLookup will work correctly off a government network however (like, at home.)

To address your other concerns...

The I Fund quote (EFD) comes from bloomberg.com. There is no equivalent quote at Yahoo (where the "others" quotes come from.)

Yes, you can run the update from the dashboard. Use the icon in the upper-right that says 'update' under it (it's the same icon as on the main page.)

Save on exit. As long as you save, your share values will be saved.

Everyone, I'm sorry for these new problems. TSPLookup has worked well for a couple of years with occasional tweaking. It's got some problems now that I can overcome. Check next week for an update.

Ed.

jayhawker

08-18-2009, 04:40 PM

TSPLookup has been updated and is available for download.

The fixes and changes in this update are:

1) Implemented a new method to get the quotes from Yahoo!

This new query method works behind government firewalls.
2) Implemented the same method for the “Others” quotes.

3) Implemented a change to the “Others” table.

The user may now enter a stock symbol in any of the 13 cells.
Previously, the user couldn’t skip any rows.
Alternately, the user may enter multiple symbols separated by commas into one cell.
There’s still a limit of 13 symbols as before regardless of the method used to enter them.
4) Removed the balloon messages that popped up when the “Others” query failed and then subsequently succeeded.

This error checking is no longer relevant as the new method should not fail. (You may never have seen these balloons.)
5) Implemented a change to get the foreign currency quotes from Yahoo!

6) Corrected a minor problem with the I Fund share price in the Dashboard.

Only showed up on weekends on tab0 and tab1.
Been there for a long time and no one mentioned it.
7) Corrected a minor problem with the G Fund changes in the Dashboard.

On tab2.
8) ALL the quotes and TSP share prices will update on the auto-open on a new day.

Previously, when you opened the application the first time on a new day, only the F C S I and the TSP share prices updated. (I thought I included this last time but it didn’t take. It works now.)
9) Fixed a behavior where the main page shifted to the right when the About form appeared on auto-open.

This was very annoying and I finally fixed it.
10) Renamed the application TSPLookup.

Was TSPLookup2.
Please delete your old versions.
11) Added the version number to the main page in the top center.

Now v2.6.
12) Added the caret (^) appropriately to stock symbols.

13) Changed the foreign quote for Italy.

MIBTEL has not updated since 5/29. I picked something else.
14) Updated the web page with a couple of new screen shots.

Whew! Major overhaul Batman!

Thanks all for your patience and support.

Please download the latest version from the link in my signature.

Ed.

jpr

08-26-2009, 08:15 AM

Thanks. It's working great!

jayhawker

08-27-2009, 11:06 AM

Thanks jpr!

TSPLookup has been updated today.

A problem on the "Todays Estimate" tab in the Dashboard was reported. The I Fund price value was not correct. I thought this only happened on the weekends and I had fixed it but... no. Anyway, I corrected it.

Please download v2.7 from the link in my signature.

Thanks,

Ed.

tsptalk

08-27-2009, 11:26 AM

Cool! I had never noticed the area where you can insert your own symbols. Very nice.

wwwtractor

08-27-2009, 04:13 PM

Are you sure it is fixed. Mine is stuck around noon.

I EFD 12:32PM ET 1483.19 -6.79 -0.46%

Something really weird going on with EFD:

Bid:0.01 x 100
Ask:199,999.98 x 40

tsptalk

08-27-2009, 04:38 PM

Yeah, it should only be about $8.10. 1483.19 must be a quote for another symbol.

jayhawker

08-27-2009, 05:59 PM

Are you sure it is fixed. Mine is stuck around noon.

I EFD 12:32PM ET 1483.19 -6.79 -0.46%

Something really weird going on with EFD:

Bid:0.01 x 100
Ask:199,999.98 x 40

Yeah, it should only be about $8.10. 1483.19 must be a quote for another symbol.

The quote is correct.

Know that this quote comes from bloomberg.com - not Yahoo! At bloomberg.com, EFD:IND (MSCI EAFE INDEX) and MXEA:IND (MSCI EAFE) are the quotes for the I Fund. (Both return the same data.) If you look up EFD at Yahoo! you get a result for an entity not at all related to the I Fund. A lookup of MXEA at Yahoo! returns nothing.

Secondly, the market for the EFD index closes at about this time (12:30PM ET) every day when the last of the foreign markets close. The price is final and won't update after that time on that day.

Hope I explained this well enough. If not, ask again.

Ed.

tsptalk

09-05-2009, 11:30 AM

I monitor our investments via morningstar. Our only TSP holding is L2040. Currently I'm using ETF symbols to represent the individual holdings of L2040 (eg SPY, EFA, VXF etc). I understand TSP funds don't have ticker symbols, but was wondering if anyone has found a good substitute lifestyle fund in the private sector with a symbol I could use to represent our L2040 holdings by a single entry. Thanks for any feedback.

KL
Hi KL -

I don't have a symbol, but I wonder if Jayhawker might be persuaded to add the L-funds to his TSP Lookup program by combining the 5 main funds using the L-fund equivalent percentage?

http://www.tsptalk.com/mb/showthread.php?t=7471

jayhawker

09-06-2009, 08:32 PM

Hi KL -

I don't have a symbol, but I wonder if Jayhawker might be persuaded to add the L-funds to his TSP Lookup program by combining the 5 main funds using the L-fund equivalent percentage?

http://www.tsptalk.com/mb/showthread.php?t=7471

There's a couple of reasons why I have avoided adding the L Funds:

1) The percentages change quarterly. I'd either have to create a table that goes out a few years - or - manipulate the values quarterly - or - update the application quarterly.

2) I would have to modify the dashboard. Honestly, the dashboard is a pain in the butt! I'd have to double the textboxes on each tab, deal with the user's share values, and write a bunch of code. I'm not saying it's impossible, but it's a daunting challenge that I don't have the time or will to take on.

Ed.

jayhawker

09-16-2009, 08:36 PM

TSPLookup has been updated today.

Once again, a problem with the I Fund quote from bloomberg.com has been fixed.

I also fixed the AEX (The Netherlands) quote.

Please download TSPLookup v2.8 from the link in my signature.

Thanks,

Ed.

Frixxxx

12-03-2009, 04:17 PM

I fund quote is down again brother.....:o

Just thought you would wanna know!:cool:

jayhawker

12-03-2009, 06:31 PM

I fund quote is down again brother.....:o

Just thought you would wanna know!:cool:

Thanks. I noticed this back on Tuesday also. Was kinda' waiting to see if corrected itself... but no. I updated TSPLookup today.

Thanks,

Ed.

Frixxxx

01-06-2010, 05:25 PM

Noticed that I Fund Countries: France, Netherlands, Sweden, Findland, Belgium, Greece and Austira are not updating.

Peace!:cool:

jayhawker

01-06-2010, 05:47 PM

Noticed that I Fund Countries: France, Netherlands, Sweden, Findland, Belgium, Greece and Austira are not updating.

Peace!:cool:

Yep, I'm aware of this. I think it's just a Yahoo! issue, but I'll look at it.

I also need to update the percentages of the foreign distribution because they were changed quite awhile ago and I hadn't noticed. And they'll probably change again soon too as the TSP tends to adjust them this time of the year.

Thanks,

Ed.

jayhawker

01-13-2010, 06:36 PM

Hi all,

I updated TSPLookup today. I fixed all the countries that weren't updating except for France. I could find no evidence that the symbol ^FCHI has changed to something else. It's just a Yahoo! thing that they'll fix eventually.

I also re-ordered the foreign countries and currencies and updated the foreign allocation percentages (for 2009.)

Enjoy!

JTH

01-13-2010, 06:53 PM

Thanks Jay, I envy your Excel skills !!!

Frixxxx

01-13-2010, 07:16 PM

Jayman!

PAYPAL Donation on the way!

jayhawker

01-13-2010, 08:04 PM

Thanks Jay, I envy your Excel skills !!!

Jayman!

PAYPAL Donation on the way!

Thanks guys!

Frixxxx

02-23-2010, 12:33 PM

jayhawker

02-24-2010, 05:32 PM

Hey JayHawker,

Nice B-ball run going, you guys are the #1 pick for March.

Hey, Just noticed that I am scheduled for a 35 cent gain in the F Fund today?

I mean I hope that's what I'm getting, but I seriously doubt it.....

Anyone else seeing this?

Frixxx, I didn't use TSPLookup yesterday so I can't say what the problem may have been. You may recall - for the F Fund only - that the TSP Fund price (on the upper-left table) is the AGG quote, but the F Fund Share price (on the upper-right table) is a calculated value of 30 year and 10 year T-bills. So, it's likely one or the other quote was wrong or off somehow. You got 5 cents which is pretty good for the F!

Yes, my Jayhawks are cruising right along. Rock Chalk!

Frixxxx

02-24-2010, 06:37 PM

Frixxx, I didn't use TSPLookup yesterday so I can't say what the problem may have been. You may recall - for the F Fund only - that the TSP Fund price (on the upper-left table) is the AGG quote, but the F Fund Share price (on the upper-right table) is a calculated value of 30 year and 10 year T-bills. So, it's likely one or the other quote was wrong or off somehow. You got 5 cents which is pretty good for the F!

Yes, my Jayhawks are cruising right along. Rock Chalk!
Kinda figured, calculation went skewed big time yesterday, it looked like 5 cents but the spreadsheet was showing 57 cents at the end of the day!!!

Thanks for lookin into it!