TSPLookup - Introduction, Comments & Questions Thread

[jayhawker]

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]

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]

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]

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

 

[jayhawker]

Jayhawker now has a separate page where you can read more information and download the utility... 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]

Your post on 350z's thread

EAFE final: -.215 or -5 cents.

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


What am I missing?

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

 

[jayhawker]

Your post on 350z's thread

is NOT 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]

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]

Your post on 350z's thread

is NOT 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]

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]

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]

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]

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]

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

 

[jayhawker]

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.

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

Thanks for your question. Ed.

 

[Ed]

How do I download file? Thanks for your input.

Ed

 

[jayhawker]

How do I download file? Thanks for your input.

Ed

Simply click on the link in my signature.

 

[RPM]

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,

 

[jayhawker]

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]

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,