View Full Version : Quicken TSP Share Price Updater
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 (http://www.techalchemy.net/tsp.php). (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.
Well, gee. TSP.gov erased the share-prices page:
<!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 (http://www.techalchemy.net/tsp.php)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!!!
:D
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.
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. :^
Update:
Okay, I have the whole system working as of right now....it is schweeeeeet. Click here (http://www.techalchemy.net/TSP/tsp.php) 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.
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 (http://www.techalchemy.net/TSP/tspcron.php). 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.
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.
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
11-08-2004, 10:22 AM
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
11-08-2004, 02:11 PM
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
11-20-2004, 03:43 AM
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.
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
11-21-2004, 01:28 AM
Rolo wrote: Tom, you still wanna incorporate that into the home page?
Sure. Or I can add it to the financial utilities page. http://www.tsptalk.com/utilities.html (http://www.tsptalk.com/utilities.html)
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
11-21-2004, 03:09 PM
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).
coolhand wrote: 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).
Yeah, it would be strictly thru a web browser and the web server will do all the number-crunching. I'm all about making things require little-to-no effort.
[line]
The Quicken TSP Share Price Updater is a bit of a misnomer. All it is is a CSV file (text) that works in Quicken. If the current CSV file does not work, then if someone posts the requirements for M$ Money, I can add one for you guys, too.
Copperhead
11-22-2004, 02:39 AM
Didn't mean to confuse anyone, but yes, Quicken 2005 can import the file. I followed the directions posted and it worked fine, other than noted.
To further explain the problem I had: I input each TSP fund in on the 6th of November. I then tried to updateas far back as possibleby cutting and pasting all the text into a notepad file then importing it into quicken. The data went back to 1 June, wellbeforethe date Ioriginally inputinto Quicken.That's when the data was corrupted. I then restored from a backup and tried again with just using from the 6th of November to present. That one worked fine.
Bottom line, I don't recommend importing data from earlier than you have TSP data in Quicken.
Hope this clarifies what happened and doesn't create more confusion.
I am puzzled. You should be able to import the entire share price history just fine, regardless of when your account was opened. I imported the entire share price history into mine (was one of my goals).
I got the impression that only the most recent date was corrupt when you tried it initially; is that correct?
Oh, yeah!I just remembered one thing: A few weeks ago, my import (a regualar update, only a few days) didn't work right. Somehow that day's updates were corrupt in the site's database and I had to delete them and refresh them. Perhaps that was the same day you updated yours...what flukish timing.
If yer up to it, you can download prices from the beginning (leave it blank) to Nov 5th and import them, just select the dates, download and import the file. Editing should not be necessary.
Copperhead
11-25-2004, 01:00 AM
Got it to work but I had to do two things different than before. First, I saved the file with a .csv extension instead of .txt. Second, I used Excel to open the file and reorder the dates from oldest to newest. From there, the update was seemless but still only showed the performance of the funds starting at the date I input as the date purchased. No issue, at least I can track it from here instead of updating manually.
Copperhead wrote: only showed the performance of the funds starting at the date I input as the date purchased.
Oooooooh! Yeah, its reporting your return, not the fund's actual performance.
Boots
12-03-2004, 09:41 PM
Office 2003 users have the ability to do a web query for data like this, and it's very quick and efficient. One problem I'm having is I can't get the data for any custom selected dates. The query won't recognize drop down selections I guess. I don't know if you can refresh the data with the query without it deleting older data then what the query comes up with.
There may not be any practical application as far as where Rolo's script goes, but I find it an easy way to make some simple data comparison calculations using excel without having to hand type any of the data. I just wish I knew how to do more with it.
vBulletin® v3.8.4, Copyright ©2000-2010, Jelsoft Enterprises Ltd.