TSP share prices for Quicken
TSP share prices for Quicken
I couldn't find an up-to-date source for TSP fund share price data in a format that could be easily imported into Quicken, so I wrote a script to retrieve it from the TSP Share Price History page.
You can download the CSV file directly. It's updated every trading day within a half-hour or so of when the TSP site is updated. For Quicken to import the price history data, you'll need to use the fund names from the CSV file as your Quicken security names.
If you prefer, you can maintain your own copy of the CSV data using the Python script.
Thanks,
Sim
You can download the CSV file directly. It's updated every trading day within a half-hour or so of when the TSP site is updated. For Quicken to import the price history data, you'll need to use the fund names from the CSV file as your Quicken security names.
If you prefer, you can maintain your own copy of the CSV data using the Python script.
Thanks,
Sim
Last edited by Simbilis on Sat Sep 16, 2017 10:15 am, edited 11 times in total.
Re: TSP share prices for Quicken
You are better than those guys at Quicken who haven’t made any significant code improvements in years, (still no TSP access) but expect users to upgrade for their few cosmetic changes, and of course new bugs. Even Vanguard provides for direct and automatic TSP access.
Re: TSP share prices for Quicken
[Revision of previous uninformed response]
Looks like Intuit is demanding a huge licensing fee for QFX from TSP, and TSP is ignoring them. That leaves us stuck. Might be time to look at GnuCash, although that won't help with the TSP problem.
Looks like Intuit is demanding a huge licensing fee for QFX from TSP, and TSP is ignoring them. That leaves us stuck. Might be time to look at GnuCash, although that won't help with the TSP problem.
Re: TSP share prices for Quicken
Kuckie,Kuckie wrote:You are better than those guys at Quicken who haven’t made any significant code improvements in years, (still no TSP access) but expect users to upgrade for their few cosmetic changes, and of course new bugs. Even Vanguard provides for direct and automatic TSP access.
How do you track the TSP from Vanguard? I've been trying to figure out how to do that and I must be missing something. Thanks.
-
- Posts: 3937
- Joined: Thu Jun 25, 2009 12:50 am
- Location: Vancouver WA
Re: TSP share prices for Quicken
I've been in the same boat for years. There is no good solution. Your choices are basically:
1. Check the current TSP share prices on the TSP web site periodically and enter those manually into Quicken (what I do every few weeks or when I feel like it)
2. Use some proxy fund for the TSP funds. There are close or identical funds you can use for all the TSP funds except the G fund. For example, the Vanguard Developed Markets Index (VIDMX) is a good proxy for the I-Fund and the Vanguard Extended Markets Index (VEXAX) is a good proxy for the S-Fund. The share prices don't match so you just have to adjust your share numbers to make the totals come out but then Quicken will track the portfolio value more or less accurately. I do this when I use the Morningstar Portfolio X-Ray so that I can evaluate my entire portfolio but I don't bother to do this in Quicken because it is easy enough to update the share prices manually when I care to. Plus, my TSP accounts are legacy accounts and not receiving active contributions.
3. Use some other tool to track your portfolio. Mint, for example, will track your TSP portfolio no problem and will update your share values. It is a mystery to me who Mint can manage to do this and not Quicken since Quicken bought Mint a year or two ago. I guess it is as the previous poster says...they are trying to extort money out of the TSP to use their propriety system and the TSP is rightly telling them to stuff it.
1. Check the current TSP share prices on the TSP web site periodically and enter those manually into Quicken (what I do every few weeks or when I feel like it)
2. Use some proxy fund for the TSP funds. There are close or identical funds you can use for all the TSP funds except the G fund. For example, the Vanguard Developed Markets Index (VIDMX) is a good proxy for the I-Fund and the Vanguard Extended Markets Index (VEXAX) is a good proxy for the S-Fund. The share prices don't match so you just have to adjust your share numbers to make the totals come out but then Quicken will track the portfolio value more or less accurately. I do this when I use the Morningstar Portfolio X-Ray so that I can evaluate my entire portfolio but I don't bother to do this in Quicken because it is easy enough to update the share prices manually when I care to. Plus, my TSP accounts are legacy accounts and not receiving active contributions.
3. Use some other tool to track your portfolio. Mint, for example, will track your TSP portfolio no problem and will update your share values. It is a mystery to me who Mint can manage to do this and not Quicken since Quicken bought Mint a year or two ago. I guess it is as the previous poster says...they are trying to extort money out of the TSP to use their propriety system and the TSP is rightly telling them to stuff it.
Re: TSP share prices for Quicken
Sorry, I found out that Vanguard has closed account aggregation linking outside accounts, like the TSP, but still provides services for existing users. I don't know why. See their comments: https://personal.vanguard.com/us/help/F ... ent.jsp#1aHow do you track the TSP from Vanguard? I've been trying to figure out how to do that and I must be missing something.
Re: TSP share prices for Quicken
Well, except scraping TSP's site for the prices See Google spreadsheet link above.texasdiver wrote:I've been in the same boat for years. There is no good solution.
It's much simpler to maintain a scraper in an online app than in a client-side app. I'm providing the TSP scrape for free because I use it myself, but if TSP changes their site and breaks it there's no guarantee I'll be able to fix it in a timely way, and that's likely to upset my "customers" (if there are any - it may just be the three of us).texasdiver wrote:It is a mystery to me who Mint can manage to do this and not Quicken since Quicken bought Mint a year or two ago. I guess it is as the previous poster says...they are trying to extort money out of the TSP to use their propriety system and the TSP is rightly telling them to stuff it.
Re: TSP share prices for Quicken
Thank you for the information. At least I know I'm not going crazy and missing something simple I should have clicked on.Kuckie wrote:Sorry, I found out that Vanguard has closed account aggregation linking outside accounts, like the TSP, but still provides services for existing users. I don't know why. See their comments: https://personal.vanguard.com/us/help/F ... ent.jsp#1aHow do you track the TSP from Vanguard? I've been trying to figure out how to do that and I must be missing something.
-
- Posts: 43
- Joined: Tue Jun 26, 2012 5:23 pm
Re: TSP share prices for Quicken
Great work and thanks a bunch for this!
How often do you plan on updating it?
How often do you plan on updating it?
Re: TSP share prices for Quicken
I'm just updating it now with today's prices. This weekend I'll rewrite the script to update the spreadsheet directly, and then it'll be synced every day as soon as the TSP site's prices are updated (roughly).TheMightyQuinn wrote:How often do you plan on updating it?
-
- Posts: 43
- Joined: Tue Jun 26, 2012 5:23 pm
Re: TSP share prices for Quicken
Strange. The spreadsheet updated last night with the correct prices however when I import into quicken it gives me strange prices around 1/2 of what they should be. I'm not sure the cause since the spreadsheet prices are correct.
Re: TSP share prices for Quicken
Weird. I don't see that problem for the funds that I hold. Can you tell which funds have the wrong prices and for what period(s), according to Quicken?
-
- Posts: 43
- Joined: Tue Jun 26, 2012 5:23 pm
Re: TSP share prices for Quicken
Sure. I just imported the latest ones and it did the same thing. Here's what Quicken tells me:
C Fund - $10.03
F Fund - $9.97
G Fund - $10
S Fund - $10.05
But this is obliviously not what's in the spreadsheet, so I'm clueless where these numbers come from.
You're not seeing the same thing on your end?
C Fund - $10.03
F Fund - $9.97
G Fund - $10
S Fund - $10.05
But this is obliviously not what's in the spreadsheet, so I'm clueless where these numbers come from.
You're not seeing the same thing on your end?
-
- Posts: 43
- Joined: Tue Jun 26, 2012 5:23 pm
Re: TSP share prices for Quicken
Okay, I may have found the problem. I changed the file name, re-imported, and it works now. My hunch was the filename "TSP Fund Price History - Quicken(2).csv", so I renamed it and it worked. I''ll try it again on the next update to see if it really is the filename.
Thanks again for doing this.
Thanks again for doing this.
Re: TSP share prices for Quicken
Those prices are the earliest ones in the file (June 2 2003). I don't think it's the price data - Quicken is somehow picking up the wrong date, and using that to price your TSP portfolio.
Changing the import file name *shouldn't* have any effect. Quicken has behaved oddly for me too, though - temporarily displaying incorrect account names, changing the opening balances of previously reconciled accounts, etc. Another reason to look at GnuCash.
Changing the import file name *shouldn't* have any effect. Quicken has behaved oddly for me too, though - temporarily displaying incorrect account names, changing the opening balances of previously reconciled accounts, etc. Another reason to look at GnuCash.
-
- Posts: 7502
- Joined: Mon Dec 17, 2007 6:32 pm
Re: TSP share prices for Quicken
When time you see (2) or other number in the filename, that usually means you downloaded a file and there was already one of the same name. Most browsers won't overwrite files on downloads.TheMightyQuinn wrote:Okay, I may have found the problem. I changed the file name, re-imported, and it works now. My hunch was the filename "TSP Fund Price History - Quicken(2).csv", so I renamed it and it worked. I''ll try it again on the next update to see if it really is the filename.
Brian
-
- Posts: 43
- Joined: Tue Jun 26, 2012 5:23 pm
Re: TSP share prices for Quicken
This time renaming the file didn't help, but opening and then resaving the file in excel worked.
-
- Posts: 43
- Joined: Tue Jun 26, 2012 5:23 pm
Re: TSP share prices for Quicken
Just to confirm, it seems I have to open and save the CSV file in Excel in order for Quicken to properly import the prices. I believe Quicken doesn't like the google docs CSV format.
Re: TSP share prices for Quicken
It would be better to post them in native CSV anyway. I just used a Google docs spreadsheet because I don't have any other way to publish it.
I'll look around for a convenient host.
I'll look around for a convenient host.
Re: TSP share prices for Quicken
I finally replicated this defect. It turns out that Quicken sometimes(?) requires the price file to be sorted in increasing date order (most recent prices at the end). I'll look at changing the order in the next export. In the meantime, you can use "Data/Sort Sheet By Column" to fix the problem if you experience it.TheMightyQuinn wrote:Strange. The spreadsheet updated last night with the correct prices however when I import into quicken it gives me strange prices around 1/2 of what they should be. I'm not sure the cause since the spreadsheet prices are correct.
-
- Posts: 43
- Joined: Tue Jun 26, 2012 5:23 pm
Re: TSP share prices for Quicken
Thanks for the info. Right now instead of saving the file I open it in Excel and then save it. After that the import works every time.
Re: TSP share prices for Quicken
Could you please download the CSV version (link removed, see above) and see if it works without the Excel step?TheMightyQuinn wrote:Right now instead of saving the file I open it in Excel and then save it. After that the import works every time.
Last edited by Simbilis on Fri Jul 19, 2013 7:03 pm, edited 1 time in total.
-
- Posts: 43
- Joined: Tue Jun 26, 2012 5:23 pm
Re: TSP share prices for Quicken
Sorry, I haven't downloaded TSP data until today.
Yes, it worked without a hitch. I was able to download and open in Quicken with the correct prices bypassing the excel step.
Thanks again!
Yes, it worked without a hitch. I was able to download and open in Quicken with the correct prices bypassing the excel step.
Thanks again!
-
- Posts: 1
- Joined: Wed Jun 26, 2013 9:31 am
Re: TSP share prices for Quicken
Hi Simbilis,
Can you share your python code with me? email: [Personally identifiable email removed by admin LadyGeek. Use PM instead.]
Thanks
Can you share your python code with me? email: [Personally identifiable email removed by admin LadyGeek. Use PM instead.]
Thanks
-
- Posts: 3
- Joined: Sat Jan 25, 2014 10:31 am
Re: TSP share prices for Quicken
Could you share with me as well? I'm really new to Quicken and could also use any set up advice you can provide. I've got my funds set up now though thanks to your spreadsheet. Could you provide me some info as to how to use the script as well? Never used one before. Thanks. [Personally identifiable email removed by admin LadyGeek. Use PM instead.]
Re: TSP share prices for Quicken
You're in luck - you don't need the script. The CSV file is updated every trading day within 10 minutes of when the TSP share prices are published. Just download and import it using "File/Import/Import security prices from CSV file".AndrewJSmith wrote:Could you provide me some info as to how to use the script as well? Never used one before.
The script is more of interest to programmers, nerds, and those concerned that I might accidentally (or on purpose ) be providing incorrect price data. If you're sure you fit in one of those categories, please PM me your email address. If you're not conversant with Python and BeautifulSoup, it's probably not for you.
-
- Posts: 43
- Joined: Tue Jun 26, 2012 5:23 pm
Re: TSP share prices for Quicken
I just want to thank you again for doing this. I download the share prices from your csv file every week or two and it's been a great help in keeping my portfolio up to date!
-
- Posts: 3
- Joined: Sat Jan 25, 2014 10:31 am
Re: TSP share prices for Quicken
Sweet. You're the man. I was going crazy trying to figure it all out.
Re: TSP share prices for Quicken
Hi,
I found this website and usds the TSP Folder in CVS in quicken, it was great to use. My question is that I have to come to this website each time I want to download the file with new prices. It went to Dropbox but it does not automatically update.
Thanks
I found this website and usds the TSP Folder in CVS in quicken, it was great to use. My question is that I have to come to this website each time I want to download the file with new prices. It went to Dropbox but it does not automatically update.
Thanks
Re: TSP share prices for Quicken
I'm not sure I understand your question. The linked CSV file is updated at the end of every trading day just after the TSP site is updated. You can see on the Dropbox site how long it's been since the last update. You need to download a fresh copy (using the same link) and import it into Quicken every time you want to update your stored TSP share prices.
-
- Posts: 3
- Joined: Sat Jan 25, 2014 10:31 am
Re: TSP share prices for Quicken
How can I setup Quicken to update my TSP shares at each contribution? Is that possible or do I need to manually edit the number of shares I have every two weeks?
Re: TSP share prices for Quicken
Right now, I know of no other way than doing it manually.
It may be possible to scrape it from the "Recent Transactions" page or the quarterly statement PDF, but I haven't really looked at that. For the time saved, it doesn't seem worth it.
It may be possible to scrape it from the "Recent Transactions" page or the quarterly statement PDF, but I haven't really looked at that. For the time saved, it doesn't seem worth it.
Re: TSP share prices for Quicken
I've finally captured a complete year of financial history in sufficient detail that I can do a clean conversion from Quicken 2013 to GnuCash. GnuCash is free/libre, and has both its own TSP site scraper (Finance::Quote::TSP) and a Python API that lets me feed it quote data directly.
Also, it isn't Quicken!
As a result, I no longer need the CSV export. There's no reason to shut it off, as it's fully automated, but I won't be paying close attention to it anymore. If you use this data and something seems wrong, please leave me a PM or a note on this thread, and I'll take a look. If anyone would like to take it over, the source is always available.
Cheers!
Sim
Also, it isn't Quicken!
As a result, I no longer need the CSV export. There's no reason to shut it off, as it's fully automated, but I won't be paying close attention to it anymore. If you use this data and something seems wrong, please leave me a PM or a note on this thread, and I'll take a look. If anyone would like to take it over, the source is always available.
Cheers!
Sim
Re: TSP share prices for Quicken
I would like to see the script -- I was planning to do something similar and I'm curious to see how you made it work. I'm a newcomer to Python and am still learning from other people's examples. That said, I would rather import the data into Octave or something other than Quicken. As far as I know, Quicken hasn't made a version of their software for my Mac in years.
Re: TSP share prices for Quicken
Looks like the TSP site now supports CSV downloads, but not in Quicken's preferred format. I'll leave my scrape up for now, but I expect that there'll be any number of conversion scripts soon.
Kudos, FRTIB
Sim
Kudos, FRTIB
Sim
Re: TSP share prices for Quicken
I tried importing this file into Quicken 2014. I was unsuccessful. Anything I can do to get it to work?
Re: TSP share prices for Quicken
You'll need a script to convert it from its current format to something Quicken can read. You could use a spreadsheet to reformat it by hand, but that kind of defeats the purpose.peddler12 wrote:I tried importing this file into Quicken 2014. I was unsuccessful. Anything I can do to get it to work?
Re: TSP share prices for Quicken
Hi I have Quicken 2014 and your file has worked perfectly. Not sure why he can't use.
Thanks again.
Thanks again.
Re: TSP share prices for Quicken
I thought Peddler was talking about the TSP fileBelendez wrote:Hi I have Quicken 2014 and your file has worked perfectly. Not sure why he can't use.
Peddler, if you meant that you can't import the file provided by TSP Share Price History page, then my previous answer applies. If you meant that you can't import the file linked at the top of this thread, then try using "File/Import/Import security prices from CSV file".
Re: TSP share prices for Quicken
Sorry I might have misread the comment.
Re: TSP share prices for Quicken
No valid prices found to import is the message I got. Glad to know it will work in 2014. I just need to figure out what I'm doing wrong.
Mike
Mike
Re: TSP share prices for Quicken
I have it straightened out. I had to rename the securities. It works now. Thank you.
Mike
Mike
Re: TSP share prices for Quicken
I spoke too soon. This must be the first day it's been available, because it's definitely broken the script - no prices after the 3/26 close. If it's not hard to fix, I'll do a manual refresh tonight.
Re: TSP share prices for Quicken
Ok, fixed, but not in a very robust way. It'll work for the time being, though.
Re: TSP share prices for Quicken
Are you telling us that the TSP website has messed up your file. That is not good news. I have love it!!!
Re: TSP share prices for Quicken
Actually, they improved their site and made my kludgy scrape (almost) obsolete.
Don't worry . My code will probably continue to work for the foreseeable future. If not, though, it's much easier now for someone else to reimplement it.
Don't worry . My code will probably continue to work for the foreseeable future. If not, though, it's much easier now for someone else to reimplement it.
Re: TSP share prices for Quicken
Is there a reason that the CSV file has not updated for six days?
Re: TSP share prices for Quicken
I forgot to restart the Dropbox agent. Sorry...peddler12 wrote:Is there a reason that the CSV file has not updated for six days?
Re: TSP share prices for Quicken
Thank you for responding so quickly and for fixing the problem.
-
- Posts: 1
- Joined: Tue Jun 10, 2014 7:59 pm
Re: TSP share prices for Quicken
Sim,
I am very much interested in your Python script. This is something I have been meaning to do for a while but never had the time. Do you think it would be possible to also scrape data off statements? Let me know best way to get file.
K
I am very much interested in your Python script. This is something I have been meaning to do for a while but never had the time. Do you think it would be possible to also scrape data off statements? Let me know best way to get file.
K