Quicken TSP Share Price Updater

**Rolo** · 08-09-2004, 10:54 PM

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** · 08-10-2004, 04:48 AM

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

Code:

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

Do we have to call them for share prices now?

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

**rokid** · 08-10-2004, 05:46 AM

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** · 08-10-2004, 05:57 AM

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** · 08-10-2004, 09:51 PM

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

**azanon** · 08-11-2004, 12:27 AM

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

**Rolo** · 08-16-2004, 11:49 PM

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** · 08-17-2004, 04:08 AM

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

Thanks!!

**dsweet** · 08-18-2004, 05:41 AM

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:Prices (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.[img][/img]

**Rolo** · 08-20-2004, 03:55 AM

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** · 08-21-2004, 05:55 PM

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** · 08-21-2004, 06:03 PM

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.