Quicken TSP Share Price Updater

Rolo · Aug 9, 2004

imported post

I am working on a Quicken/TSP semi-automatic updater.

The goal is to be able to click on a link and Quicken automatically imports the prices. However, the OFX (WebConnect) parameters are a little hard to find and confusing; I haven't the patience at the moment and I am not sure it will even work.

So, the immediate goal is to be able to download a file with which you import into Quicken. This does work.

So far, my little web script converts the TSP share prices (muahahaha) into Quicken-friendly CSV format. It processes whatever is currently displayed on the TSP site (muahahaha).

If you already have the TSP funds entered into Quicken, then edit them:
- Set Type to "Mutual Fund"
- Set ticker symbols to TSPGXX, TSPFXX, TSPCXX, TSPSXX, TSPIXX

Click here. (You may want to shift-click to open in a new window)
Highlight the dates you want (the most recent is first) and copy them.
Paste your selection into a text editor (i.e. Notepad) and save it.
Go to the Investing Center in Quicken.
Click File | Import | Import Prices and enter the file's name that you just saved.
Check the price history to make sure the dates/prices are correct.
Post your results here along with which version of Quicken you are using.

My concern right now is the date format. Try importing only two or three records so you do not add corrupt data to your real data. You could also name your real TSP equities something other than TSP?XX and use TSP?XX for testing only. I appreciate any help and thank TSP.gov for pissing me off enough to give me the drive to do this.

This is functionalpreliminary testing, by no means a final product. My goal is to have all price history stored in a SQL database and a page where you can select the dates that you want and the server will send you a file.

Rolo · Aug 10, 2004

imported post

Well, gee. TSP.gov erased the share-prices page:

Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1"></HEAD>
<BODY></BODY></HTML>

Do we have to call them for share prices now?

Well...the script has error-checking now. :?

rokid · Aug 10, 2004

imported post

Well, since tsp.gov chooses to spend their time monitoring this web site, RATHER THAN MONITORING THEIR OWN, perhaps, we can communicate to them in this indirect way:

HEY, TSP.GOV,

YOUR CURRENT RATES PAGE IS BROKEN AND WE, YOUR HUMBLE CUSTOMERS AND SHARE HOLDERS, WOULD LIKE IT FIXED!!!

Rolo · Aug 10, 2004

imported post

To the TSP bureaucrat (yes, bureaucrat, since a real IT person works to disseminate information efficiently, not to withold it) that is obviously reading this forum:

[line]

What are you trying to accomplish? What is TSP.gov's goal?

Did erasing the share-price web page help anything? To what end?

What is the reason for this?

Not bandwidth, for I would have saved you some by reducinga thousandusers' page hits to one server hit.

You do not want people to confuse third-party sites with the TSP site. Iknow this, and believe me, this potential third party does not want his siteto be confused with the TSP site; it works both ways. This is why we have disclaimers. If lawnmowers can have "Do not use indoors" disclaimers, web sites can say "We're not affiliated with TSP.gov yadda yadda yadda".

To the issue of TSP.gov's image I will point out that all of the third party sites look professional and TSP.gov itself looks cheesy. A bureaucrat armed with an eMachine withMicrosoft FrontPagedoes not a good webmaster make--content and presentation do.

For yours, and everyone else's sake, grow up.

[line]

Disclaimer: Rolo, hitherto ("I"), is not directlyaffiliated with TSPTalk.com, but is only some schmuck who frequents it.

Rolo · Aug 10, 2004

imported post

Hmm...back up. TSP.gov was upgrading one of their web servers last night...maybe that was it....heh.

azanon · Aug 10, 2004

imported post

It needed to be said anyway though Rolo, some of that. :^

Rolo · Aug 16, 2004

imported post

Update:

Okay, I have the whole system working as of right now....it is schweeeeeet. Click here to get a downloadable csv file to import directly into Quicken. You can even select dates.

Each hit does NOT go to the TSP.gov site; your downloaded file is generated locally on-the-fly.

This file can also be used in Excel. We could come up with a standard worksheet so that everyone can just paste the file into their own spreadsheet and eliminate manual entry altogether.

The current format:

Fund,price,MM/DD/YYYY (date descending)

TSPGXX,10.51,08/16/2004
TSPFXX,10.22,08/16/2004
TSPCXX,11.42,08/16/2004
TSPSXX,12.06,08/16/2004
TSPIXX,12.93,08/16/2004
TSPGXX,10.51,08/13/2004
TSPFXX,10.23,08/13/2004
TSPCXX,11.27,08/13/2004
TSPSXX,11.87,08/13/2004
TSPIXX,12.81,08/13/2004
TSPGXX,10.51,08/12/2004
TSPFXX,10.21,08/12/2004
TSPCXX,11.25,08/12/2004
TSPSXX,11.87,08/12/2004
TSPIXX,12.8,08/12/2004

I can make other formats as well. I had a blast putting this together and am glad I do not have to do manual data entry anymore!

tsptalk · Aug 17, 2004

imported post

Nice job Rolo. :^ If you get some time maybe you can write some simple instructions for us :h. That would be great!

Thanks!!

dsweet · Aug 18, 2004

imported post

Rolo,
Your awesome. I just installed Quicken 2005 for Mac

I had been manually updating prices when I loaded transactions

I did find instructions in the User guide that maybe helpful to others if their Quicken program will not read the CSV file.

To make your CSV file impotrable.

Inside a text editing program (notepad, word, text edit etc...)

• The first line in the file must be: !Type

rices (emotion is a P)
• Use either of these formats for the price data:
ABC, 123.456, 12/31/96
ABC 123.456 12/31/96
• The last line in the file must be: ^ followed by a carriage return

When your done save the file as a .QIF

then follow the import instructions.

It's great and your great

D.S.

Rolo · Aug 19, 2004

imported post

Nonono! This is NOT a .qif file...I tried that, it does not work for share prices, only for register transactions.

1. Go to the INVESTING CENTER display.

2. Click FILE > IMPORT > IMPORT PRICES

3. Tell it the name of the file that you downloaded. The date is inconsequential since the file contains dates.

If the prices are not updated (I keep forgetting to refresh it), browse here and wait. You won't see anything, but let it finish (about 10 secs.) then go to the original link. This is only temporary.

Tom: I just missed ya online, heh.

Rolo · Aug 21, 2004

imported post

Ack! ..had a bug...rather Quicken 2004 does. Apparently, it cannot understand the imported dates 100% of the time. My last few imports did not work properly.

** I changed the date format to YYYY/MM/DD to get it to work. My regional settings always have been YYYY-MM-DD and this may play a part in it.

** I also changed the order of the csv file to show the most recent price on the bottom (lists top-down, chronologically) in case the import fails, the most recent share price is entered as of the current date.

** Change to step 1: Click on INVESTING CENTER. Click on PORTFOLIO tab. Only then will "Import Prices" in step 2 be available.

Is anyone using this Quicken thing, or just I? (I don't mind, I would have written this just for myself anyway, I just want some feedback if others are using it.)

dsweet · Aug 21, 2004

imported post

Trust me ROLO your updater works with my instructions. I have successfully used your TSP updater twice... The import functions you speak of are different in Quicken for MAAC 2005.

Rolo · Aug 21, 2004

imported post

dsweet wrote:

Trust me ROLO your updater works with my instructions. I have successfully used your TSP updater twice... The import functions you speak of are different in Quicken for MAAC 2005.

Can you supply those instructions? (I don't have a Mac to test it, nor do I have Quicken 2005, yet.)

Verify your dates/share prices just to be sure. My first import worked (when I imported ALL share prices), however, my succeeding imports did not.

When I tried using the QIF format, it entered bogus transactions into my TSP register.

Shazbat · Nov 8, 2004

imported post

Wouldn't it be nice if TSP just allowed Microsoft Money and Quicken downloads like all of the other respectable financial institutions? Life could be so much simpler and hey, we would even be able to track our 'most valuable retirement benefit!'

tsptalk · Nov 8, 2004

imported post

Shazbat wrote:

Wouldn't it be nice if TSP just allowed Microsoft Money and Quicken downloads like all of the other respectable financial institutions? Life could be so much simpler and hey, we would even be able to track our 'most valuable retirement benefit!'

Yes. I get asked for the TSP Fund symbols about every day.

Welcome Shazbat!

Copperhead · Nov 19, 2004

imported post

I just updated to Quicken 2005 and decided to add my TSP information so I can track it. I started working my way backwards inputting the daily information manually and soon decided there had to be an easier way. One quick Google search later and I ended up at this site. The download worked great except for when I selected dates that predated my TSP account transactions (input in Quicken). Doing so caused the most current date in my portfolio to list the bottom data in the file. My mistake, nothing you can do to fix that.

I commend your efforts and think a more prominent link on the homepage to your Quicken work-a-round is in order.

Rolo · Nov 20, 2004

imported post

Copperhead wrote:

The download worked great except for when I selected dates that predated my TSP account transactions (input in Quicken). Doing so caused the most current date in my portfolio to list the bottom data in the file. My mistake, nothing you can do to fix that.

I'm not sure I follow exactly, but basically what you are saying is that the first share price date (like Jul 02 or something) snuck into Nov 19, 2004's spot..?

So..the big question I had, I think you answered: Quicken 2005 can import share prices from a CSV file just like the prior version can?

I haven't upgraded to 2005 yet...I have to make sure all my stuff will still work with the new formats they are monkeying with.

Copperhead wrote:

I commend your efforts and think a more prominent link on the homepage to your Quicken work-a-round is in order.

Thanks for the compliment!

Tom, you still wanna incorporate that into the home page?

tsptalk · Nov 20, 2004

imported post

Rolo wrote:

Tom, you still wanna incorporate that into the home page?

Sure. Or I can add it to the financial utilities page. [url]http://www.tsptalk.com/utilities.html[/url]

Rolo · Nov 21, 2004

imported post

A logical choice...I should have said "the site" rather than "the home page".

I'll get some tinkering done in order to do that...to have your site connect into my database.

Another idea I have is to enable members to enter their transactions on the site, to enter them into the database (which already holds daily share prices), and their returns and transactions are automatically updated and displayed. Do we have enough people interested in doing this?

coolhand · Nov 21, 2004

imported post

Rolo wrote:

Another idea I have is to enable members to enter their transactions on the site, to enter them into the database (which already holds daily share prices), and their returns and transactions are automatically updated and displayed. Do we have enough people interested in doing this?

I like that idea and would participate. It sounds like it is independent of any personal financial software we useat home (I use MS Money).

Quicken TSP Share Price Updater

Active member

Active member

Active member

Active member

Active member

Member

Active member

Moderator

New member

Active member

Active member

New member

Active member

New member

Moderator

New member

Active member

Moderator

Active member

Well-known member

Similar threads