TSP share prices for Quicken

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
gtd98765
Posts: 952
Joined: Sun Jan 08, 2017 3:15 am

Re: TSP share prices for Quicken

Post by gtd98765 »

Just want to check understanding on how to use Simbilis's web site. Please let me know if I have this wrong.

Setup
1. Create a Quicken Account for the TSP
2. Create security names in line with the names in the .csv file, e.g. TSPLINCOME.
3. Enter into the Quicken TSP account the number of shares of each TSP fund you have.

Daily or as often as you want
1. Click on https://likhite.net/scrape/tspQuicken.csv to download the file to your desktop.
2. Import this file into Quicken using the File Import/Import security prices from .CSV file

Yes?
jake_oif
Posts: 2
Joined: Sat Feb 22, 2020 11:02 am

Re: TSP share prices for Quicken

Post by jake_oif »

jsprag wrote: Sun Feb 23, 2020 3:51 am
jake_oif wrote: Sat Feb 22, 2020 11:05 am I've been a long time user of the script, but I'm trying to update it to Python 3 now that Python 2 is deprecated. Running into an issue, so I figured I'd check here to see if maybe Simbilis had already updated to Python 3. I am a complete new to Python user, so it's bee interesting.
See if this works for you in Python 3.
Perfect! Appreciate it.
jsprag
Posts: 243
Joined: Sun Nov 26, 2017 10:25 am

Re: TSP share prices for Quicken

Post by jsprag »

jsprag wrote: Sun Feb 23, 2020 3:51 am
jake_oif wrote: Sat Feb 22, 2020 11:05 am I've been a long time user of the script, but I'm trying to update it to Python 3 now that Python 2 is deprecated. Running into an issue, so I figured I'd check here to see if maybe Simbilis had already updated to Python 3. I am a complete new to Python user, so it's bee interesting.
See if this works for you in Python 3.
Script updated to reflect 1 July changes to TSP funds:
- Remove L 2020 fund
- Add L 2060 fund
- Add L 20x5 funds

Note: This is a Python 3 adaptation of Simbilis' original script. If it's broken, blame me. If it works, thank him!
Last edited by jsprag on Thu Jul 02, 2020 1:47 pm, edited 2 times in total.
Topic Author
Simbilis
Posts: 62
Joined: Sat Nov 03, 2012 12:29 pm

Re: TSP share prices for Quicken

Post by Simbilis »

jsprag wrote: Thu Jul 02, 2020 11:31 am Script updated to reflect 1 July changes to TSP funds:
- Remove L 2020 fund
- Add L 2060 fund
- Add L 20x5 funds
Courtesy of jsprag, the new funds are now in the csv file. I'm running his code now. Except I'm Simbilis, not Simbills :wink:.
jsprag
Posts: 243
Joined: Sun Nov 26, 2017 10:25 am

Re: TSP share prices for Quicken

Post by jsprag »

Simbilis wrote: Thu Jul 02, 2020 1:29 pm
jsprag wrote: Thu Jul 02, 2020 11:31 am Script updated to reflect 1 July changes to TSP funds:
- Remove L 2020 fund
- Add L 2060 fund
- Add L 20x5 funds
Courtesy of jsprag, the new funds are now in the csv file. I'm running his code now. Except I'm Simbilis, not Simbills :wink:.
Fixed! :oops:
impdust
Posts: 22
Joined: Sun Sep 07, 2014 2:41 pm

Re: TSP share prices for Quicken

Post by impdust »

Thank you both. I'm sure I'm not the only one using this, and it's greatly appreciated. :beer
gtd98765
Posts: 952
Joined: Sun Jan 08, 2017 3:15 am

Re: TSP share prices for Quicken

Post by gtd98765 »

FYI: The July 13 prices were not in the file I downloaded this morning, July 14. The prices stop July 10.

But in general, thanks so much for creating and maintaining this tool!
Topic Author
Simbilis
Posts: 62
Joined: Sat Nov 03, 2012 12:29 pm

Re: TSP share prices for Quicken

Post by Simbilis »

gtd98765 wrote: Tue Jul 14, 2020 8:31 am FYI: The July 13 prices were not in the file I downloaded this morning, July 14. The prices stop July 10.
The bad news: An update to the TSP website has broken the script, and I don't think it's worth fixing it. Because...

The good news: After 7+ years, FRTIB has finally rendered this thing obsolete. You can now download a CSV file directly from https://www.tsp.gov/fund-performance/sh ... e-history/.

Woot! :sharebeer It was never a good idea to get this data from some rando on the Internet anyway ;)
impdust
Posts: 22
Joined: Sun Sep 07, 2014 2:41 pm

Re: TSP share prices for Quicken

Post by impdust »

I may be missing something simple, but if I pull down
https://secure.tsp.gov/components/CORS/ ... download=1
or
https://secure.tsp.gov/components/CORS/ ... download=0
the data is in the wrong format to load into Quicken.

Don't we still need a script to convert it to the right format?

Just an observation, but the download=1 version does not pull the new L funds, download=0 does.
Topic Author
Simbilis
Posts: 62
Joined: Sat Nov 03, 2012 12:29 pm

Re: TSP share prices for Quicken

Post by Simbilis »

impdust wrote: Tue Jul 14, 2020 5:13 pm the data is in the wrong format to load into Quicken.

Don't we still need a script to convert it to the right format?
Argh, you're right.
Topic Author
Simbilis
Posts: 62
Joined: Sat Nov 03, 2012 12:29 pm

Re: TSP share prices for Quicken

Post by Simbilis »

Ok, fixt. Please retry. It's been so long, I forgot the problem that was being solved :oops:

Thanks @impdust for the new URL.
gtd98765
Posts: 952
Joined: Sun Jan 08, 2017 3:15 am

Re: TSP share prices for Quicken

Post by gtd98765 »

It now works for me. Thanks again!
impdust
Posts: 22
Joined: Sun Sep 07, 2014 2:41 pm

Re: TSP share prices for Quicken

Post by impdust »

Thanks @Simbilis for continuing to support it. You're the best "rando on the Internet"! :D

For what it's worth, I submitted feedback to them requesting the ability to download in the format Quicken needs, and letting them know about the missing L funds in the one version.
Topic Author
Simbilis
Posts: 62
Joined: Sat Nov 03, 2012 12:29 pm

Re: TSP share prices for Quicken

Post by Simbilis »

The TSP site revamp has broken my scraper, and after 9 years I'm ready to let someone else deal with the thing. The code and data through 5/26/2022 will be available for a while (until my EC2 reservation runs out, or I find another use for the instance).

Good luck, folks!
impdust
Posts: 22
Joined: Sun Sep 07, 2014 2:41 pm

Re: TSP share prices for Quicken

Post by impdust »

@Simbilis Thanks for supporting it all these years. I found it extremely useful and I'll miss it. But, I do see why you don't want to mess with it any further, that new TSP site is a mess. Best of luck you to you.
mysteriousclam
Posts: 8
Joined: Fri Jun 03, 2022 4:48 pm

Re: TSP share prices for Quicken

Post by mysteriousclam »

As a frequent user of the original script, I felt compelled to create one that works (at least for now) on the new website. I'm a novice, hobby programmer, so I welcome any improvements to the script!

@Simbilis and @jsprag - Thank you for having inspired me!

Code: Select all

#!/usr/bin/python
# script to scrape tsp fund prices from tsp.gov
# adapted from Python 2 script created by user Simbilis on Bogleheads.org
# https://www.bogleheads.org/forum/viewtopic.php?f=1&t=108388

import requests
import csv
from datetime import datetime, timedelta, date
import sys

fundTag = {
    'L Income'  : 'TSPLINCOME',
    'L 2025' : 'TSPL2025',
    'L 2030' : 'TSPL2030',
    'L 2035' : 'TSPL2035',
    'L 2040' : 'TSPL2040',
    'L 2045' : 'TSPL2045',
    'L 2050' : 'TSPL2050',
    'L 2055' : 'TSPL2055',
    'L 2060' : 'TSPL2060',
    'L 2065' : 'TSPL2065',
    'G Fund' : 'TSPGFUND',
    'F Fund' : 'TSPFFUND',
    'C Fund' : 'TSPCFUND',
    'S Fund' : 'TSPSFUND',
    'I Fund' : 'TSPIFUND'}

priceHistoryFile = 'tspQuicken.csv'

lastDate = ''
try:
    quickenReader = csv.reader(open(priceHistoryFile, 'r'))
    lastDate = [row for row in quickenReader][-1][2]
except:
    lastDate = '01/01/2003'

startDate = (datetime.strptime(lastDate, '%m/%d/%Y') + timedelta(1)).strftime('%Y%m%d')
endDate = date.today().strftime('%m/%d/%Y')
if lastDate == endDate:
    print('already have prices through', endDate)
    sys.exit()

print('checking for new prices starting on', startDate)
tspSharePricePageUrl = 'https://www.tsp.gov/data/getSharePricesRaw_startdate_20220424_enddate_20220524_Lfunds_1_InvFunds_1_download_0.html'
headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.63 Safari/537.36'}

page = requests.get(tspSharePricePageUrl, headers=headers)

reader = csv.reader(page.text.splitlines(), delimiter=',')
rows = [row for row in reader if len(row) > 0]
tagRow = rows[0]
foundNew = False

newRows = []
for row in rows[1:]:
    currDate = datetime.strptime(row[0], '%b %d. %Y').strftime('%m/%d/%Y')
    if datetime.strptime(currDate, '%m/%d/%Y') >= datetime.strptime(startDate, '%Y%m%d'):
        for i in range(1, len(row)):
            tag = tagRow[i].lstrip()
            if tag in fundTag:
                try:
                    price = float(row[i])
                except:
                    continue
                newRows.append([fundTag[tag], price, currDate])
                foundNew = True
                print('found', [fundTag[tag], price, currDate])

if foundNew:
    with open(priceHistoryFile, "a", newline='') as file:
        writer = csv.writer(file)
        writer.writerows(newRows)

sys.exit()
Last edited by mysteriousclam on Sat Jun 18, 2022 7:26 pm, edited 2 times in total.
Topic Author
Simbilis
Posts: 62
Joined: Sat Nov 03, 2012 12:29 pm

Re: TSP share prices for Quicken

Post by Simbilis »

@impdust @mysteriousclam :beer
gtd98765
Posts: 952
Joined: Sun Jan 08, 2017 3:15 am

Re: TSP share prices for Quicken

Post by gtd98765 »

Thanks mysteriousclam!
LibertyPrime
Posts: 2
Joined: Wed Jun 08, 2022 10:19 am

Re: TSP share prices for Quicken

Post by LibertyPrime »

Thank you @mysteriousclam
I'm curious on how you found that new URL of https://www.tsp.gov/data/getSharePrices ... oad_0.html
mysteriousclam
Posts: 8
Joined: Fri Jun 03, 2022 4:48 pm

Re: TSP share prices for Quicken

Post by mysteriousclam »

LibertyPrime wrote: Wed Jun 08, 2022 10:24 am Thank you @mysteriousclam
I'm curious on how you found that new URL of https://www.tsp.gov/data/getSharePrices ... oad_0.html
I found it by using the Network panel in Chrome's Developer tool and looking through the files the website https://www.tsp.gov/fund-performance/sh ... e-history/ loads. You can find out more about that panel here: https://developer.chrome.com/docs/devtools/network/
LibertyPrime
Posts: 2
Joined: Wed Jun 08, 2022 10:19 am

Re: TSP share prices for Quicken

Post by LibertyPrime »

mysteriousclam wrote: Wed Jun 08, 2022 7:40 pm
LibertyPrime wrote: Wed Jun 08, 2022 10:24 am Thank you @mysteriousclam
I'm curious on how you found that new URL of https://www.tsp.gov/data/getSharePrices ... oad_0.html
I found it by using the Network panel in Chrome's Developer tool and looking through the files the website https://www.tsp.gov/fund-performance/sh ... e-history/ loads. You can find out more about that panel here: https://developer.chrome.com/docs/devtools/network/
Ah yes, I've used that tool in the past but kind of forgot about it. Good find! Thanks for responding. I have a PowerShell script that also broke with the TSP site changes. I'll be working on it soon to get data from this new link instead.
USCJim
Posts: 2
Joined: Tue Jun 14, 2022 7:28 am

Re: TSP share prices for Quicken

Post by USCJim »

Thanks mysteriousclam! Okay, so I've been using the previous link for a long time and it worked like a charm. And I see the code written my @mysteriousclam. So what's the best way for a new guy to get started using the code to download and format TSP prices? I'm totally clueless but willing to learn. Tanks!
gtd98765
Posts: 952
Joined: Sun Jan 08, 2017 3:15 am

Re: TSP share prices for Quicken

Post by gtd98765 »

I have not done any computer programming in 30 years, so I am far from an expert. But this is what I did, and it worked.

1. Download the Thonny Python interpreter from Thonny.org. Install it on your computer.
2. Select, and then Copy the program from mysteriousclam's post,
3. Paste it in to the top Thonny window.
4. Edit only one line of the program so you can put the results of running it in a known location like Downloads (I have Windows, may be different for Mac):

Change this line to:
priceHistoryFile = 'tspQuicken.csv'
To this line:
priceHistoryFile = 'C:\\Users\\Regular Work Account\\Downloads\\tspQuicken.csv'

5. Run the program by selecting Run/Run current script.

This puts the tspQuicken.csv prices for the last month or so in your Downloads folder.

Good luck.

edit: Line 1 expand.
mysteriousclam
Posts: 8
Joined: Fri Jun 03, 2022 4:48 pm

Re: TSP share prices for Quicken

Post by mysteriousclam »

USCJim wrote: Tue Jun 14, 2022 7:35 am Thanks mysteriousclam! Okay, so I've been using the previous link for a long time and it worked like a charm. And I see the code written my @mysteriousclam. So what's the best way for a new guy to get started using the code to download and format TSP prices? I'm totally clueless but willing to learn. Tanks!
It should be as easy as follows (OS agnostic):
  1. Download Python 3.
  2. Create a new text file with a '.py' extension that consists of the code I included in my original post.
  3. Run the file you created. On Windows, you should be able to double click on the file and run it.
If you have the 'tspQuicken.csv' file in the same folder as the '.py' file you created, there should be no need to alter the code.
TSPUser12345
Posts: 1
Joined: Sat Jun 25, 2022 2:02 pm

Re: TSP share prices for Quicken

Post by TSPUser12345 »

I downloaded Python, created the text document with the information provided above and I tried to run the script however I'm receiving an error "ModuleNotFoundError: No module named 'requests'." Any ideas? This is probably a simplistic question but I'm new the Python.
ERAM
Posts: 5
Joined: Thu Oct 26, 2017 5:30 pm

Re: TSP share prices for Quicken

Post by ERAM »

I tried the procedures outlined above, for using Thonny Python, and get the same error. I am unfamiliar with Python also, so any help to get this working would be appreciated.
gtd98765
Posts: 952
Joined: Sun Jan 08, 2017 3:15 am

Re: TSP share prices for Quicken

Post by gtd98765 »

TSPUser12345 wrote: Sat Jun 25, 2022 2:11 pm I downloaded Python, created the text document with the information provided above and I tried to run the script however I'm receiving an error "ModuleNotFoundError: No module named 'requests'." Any ideas? This is probably a simplistic question but I'm new the Python.
Also not a Python expert. But try this:

In Thonny, after you have pasted in the program
-Click on Tools, Manage Packages
-Type "requests" in the search box, then click "Search on PyPI"
-The top search item for me is "requests", "Python HTTP for humans"
- Click "requests" and "Install"
- Run the program again.
- If there are other modules missile like csv or sys, search for them and install them the same way

Good luck, hope this works for you. Once again, I am using Windows; if you are on a Mac this may be different.
ERAM
Posts: 5
Joined: Thu Oct 26, 2017 5:30 pm

Re: TSP share prices for Quicken

Post by ERAM »

Thanks gtd98765,
You are an expert to me, because your solution worked. Thank you for taking the time to help me out, it is much appreciated. I am also very thankful for the code that was provided above.
User avatar
user9532
Posts: 257
Joined: Sun Dec 18, 2016 9:08 pm
Contact:

Re: TSP share prices for Quicken

Post by user9532 »

If you go to the TSP share price page and select "View Page Source" on your browser, you can see that they have the code for downloading share prices in CSV, but it is commented out, meaning the code is not active. Someday probably they will activate it.
mysteriousclam
Posts: 8
Joined: Fri Jun 03, 2022 4:48 pm

Re: TSP share prices for Quicken

Post by mysteriousclam »

Looks like TSP changed their files a bit, so the old code won't work. The URL

Code: Select all

https://www.tsp.gov/data/getSharePricesRaw_startdate_20220424_enddate_20220524_Lfunds_1_InvFunds_1_download_0.html
needs to be replaced with

Code: Select all

https://www.tsp.gov/data/getSharePricesRaw_startdate_20220424_enddate_20220524_Lfunds_1_InvFunds_1_download_0.csv
Below is updated code, which currently works. No doubt, the URL will continue to change. Given the strange URL, I assume whoever is programming the website made a mistake.

Code: Select all

#!/usr/bin/python
# script to scrape tsp fund prices from tsp.gov
# adapted from Python 2 script created by user Simbilis on Bogleheads.org
# https://www.bogleheads.org/forum/viewtopic.php?f=1&t=108388

import requests
import csv
from datetime import datetime, timedelta, date
import sys

fundTag = {
    'L Income'  : 'TSPLINCOME',
    'L 2025' : 'TSPL2025',
    'L 2030' : 'TSPL2030',
    'L 2035' : 'TSPL2035',
    'L 2040' : 'TSPL2040',
    'L 2045' : 'TSPL2045',
    'L 2050' : 'TSPL2050',
    'L 2055' : 'TSPL2055',
    'L 2060' : 'TSPL2060',
    'L 2065' : 'TSPL2065',
    'G Fund' : 'TSPGFUND',
    'F Fund' : 'TSPFFUND',
    'C Fund' : 'TSPCFUND',
    'S Fund' : 'TSPSFUND',
    'I Fund' : 'TSPIFUND'}

priceHistoryFile = 'tspQuicken.csv'

lastDate = ''
try:
    quickenReader = csv.reader(open(priceHistoryFile, 'r'))
    lastDate = [row for row in quickenReader][-1][2]
except:
    lastDate = '01/01/2003'

startDate = (datetime.strptime(lastDate, '%m/%d/%Y') + timedelta(1)).strftime('%Y%m%d')
endDate = date.today().strftime('%m/%d/%Y')
if lastDate == endDate:
    print('already have prices through', endDate)
    sys.exit()

print('checking for new prices starting on', startDate)
tspSharePricePageUrl = 'https://www.tsp.gov/data/getSharePricesRaw_startdate_20220424_enddate_20220524_Lfunds_1_InvFunds_1_download_0.csv'
headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.63 Safari/537.36'}

page = requests.get(tspSharePricePageUrl, headers=headers)

reader = csv.reader(page.text.splitlines(), delimiter=',')
rows = [row for row in reader if len(row) > 0]
tagRow = rows[0]
foundNew = False

newRows = []
for row in rows[1:]:
    currDate = datetime.strptime(row[0], '%b %d. %Y').strftime('%m/%d/%Y')
    if datetime.strptime(currDate, '%m/%d/%Y') >= datetime.strptime(startDate, '%Y%m%d'):
        for i in range(1, len(row)):
            tag = tagRow[i].lstrip()
            if tag in fundTag:
                try:
                    price = float(row[i])
                except:
                    continue
                newRows.append([fundTag[tag], price, currDate])
                foundNew = True
                print('found', [fundTag[tag], price, currDate])

if foundNew:
    with open(priceHistoryFile, "a", newline='') as file:
        writer = csv.writer(file)
        writer.writerows(newRows)

sys.exit()
gtd98765
Posts: 952
Joined: Sun Jan 08, 2017 3:15 am

Re: TSP share prices for Quicken

Post by gtd98765 »

Well, the program stopped working for me. Did TSP change their website again? This is the error that Thonny gives me:
page = requests.get(tspSharePricePageUrl, headers=headers)
NameError: name 'tspSharePricePageUrl' is not defined
mysteriousclam
Posts: 8
Joined: Fri Jun 03, 2022 4:48 pm

Re: TSP share prices for Quicken

Post by mysteriousclam »

gtd98765 wrote: Sat Jul 09, 2022 8:37 am Well, the program stopped working for me. Did TSP change their website again? This is the error that Thonny gives me:
page = requests.get(tspSharePricePageUrl, headers=headers)
NameError: name 'tspSharePricePageUrl' is not defined
It's still working. It looks like in your code you haven't defined tspSharePricePageUrl. So make sure the following appears before your requests.get:

Code: Select all

tspSharePricePageUrl = 'https://www.tsp.gov/data/getSharePricesRaw_startdate_20220424_enddate_20220524_Lfunds_1_InvFunds_1_download_0.csv'
headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.63 Safari/537.36'}
gtd98765
Posts: 952
Joined: Sun Jan 08, 2017 3:15 am

Re: TSP share prices for Quicken

Post by gtd98765 »

Thank you mysteriousclam, that did it. I wonder how I managed to delete that line?
mysteriousclam
Posts: 8
Joined: Fri Jun 03, 2022 4:48 pm

Re: TSP share prices for Quicken

Post by mysteriousclam »

New code given website changes

Code: Select all

#!/usr/bin/python
# script to scrape tsp fund prices from tsp.gov
# adapted from Python 2 script created by user Simbilis on Bogleheads.org
# https://www.bogleheads.org/forum/viewtopic.php?f=1&t=108388

import requests
import csv
from datetime import datetime, timedelta, date
import sys

fundTag = {
    'L Income'  : 'TSPLINCOME',
    'L 2025' : 'TSPL2025',
    'L 2030' : 'TSPL2030',
    'L 2035' : 'TSPL2035',
    'L 2040' : 'TSPL2040',
    'L 2045' : 'TSPL2045',
    'L 2050' : 'TSPL2050',
    'L 2055' : 'TSPL2055',
    'L 2060' : 'TSPL2060',
    'L 2065' : 'TSPL2065',
    'G Fund' : 'TSPGFUND',
    'F Fund' : 'TSPFFUND',
    'C Fund' : 'TSPCFUND',
    'S Fund' : 'TSPSFUND',
    'I Fund' : 'TSPIFUND'}

priceHistoryFile = 'tspQuicken.csv'

lastDate = ''
try:
    quickenReader = csv.reader(open(priceHistoryFile, 'r'))
    lastDate = [row for row in quickenReader][-1][2]
except:
    lastDate = '01/01/2003'

startDate = (datetime.strptime(lastDate, '%m/%d/%Y') + timedelta(1)).strftime('%Y%m%d')
endDate = date.today().strftime('%m/%d/%Y')
if lastDate == endDate:
    print('already have prices through', endDate)
    sys.exit()

print('checking for new prices starting on', startDate)
tspSharePricePageUrl = 'https://www.tsp.gov/data/fund-price-history.csv'
headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.63 Safari/537.36'}

page = requests.get(tspSharePricePageUrl, headers=headers)

reader = csv.reader(page.text.splitlines(), delimiter=',')
rows = [row for row in reader if len(row) > 0]
tagRow = rows[0]
foundNew = False

newRows = []
for row in rows[1:]:
    try:
        currDate = datetime.strptime(row[0], '%Y-%m-%d').strftime('%m/%d/%Y')
    except:
        currDate = '01/01/2002'
    if datetime.strptime(currDate, '%m/%d/%Y') >= datetime.strptime(startDate, '%Y%m%d'):
        for i in range(1, len(row)):
            tag = tagRow[i].lstrip()
            if tag in fundTag:
                try:
                    price = float(row[i])
                except:
                    continue
                newRows.append([fundTag[tag], price, currDate])
                foundNew = True
                print('found', [fundTag[tag], price, currDate])

if foundNew:
    with open(priceHistoryFile, "a", newline='') as file:
        writer = csv.writer(file)
        writer.writerows(newRows)

sys.exit()
User avatar
user9532
Posts: 257
Joined: Sun Dec 18, 2016 9:08 pm
Contact:

Re: TSP share prices for Quicken

Post by user9532 »

If you paste that url into the browser (Chrome), it downloads the history in csv format. So do you really need the python script?
quietseas
Posts: 901
Joined: Fri Dec 27, 2013 3:43 pm

Re: TSP share prices for Quicken

Post by quietseas »

After years of entering transactions every pay period, a few weeks ago I switched to "Simple Tracking" in Quicken and will only update share values every 30-60 days. This was in part prompted by the new website and statements that make it impossible to tell how many shares of a fund are in Traditional and Roth, but I'm fine with the simplified approach and no longer feel the need for more frequent share price updates. Will roll over to a brokerage IRA as soon as I can. Quicken also does not out of the box support Roth 401K contributions which requires some kludging as well.
impdust
Posts: 22
Joined: Sun Sep 07, 2014 2:41 pm

Re: TSP share prices for Quicken

Post by impdust »

user9532 wrote: Sat Jul 16, 2022 11:11 am If you paste that url into the browser (Chrome), it downloads the history in csv format. So do you really need the python script?
The format that quicken requires is one symbol, price, and date per line. The downloaded CSV file is one line per date with each price in a different columns, the symbols are only on the top row. You don't necessarily have to use Python, but you have to use something to convert the file before you can import it into Quicken.

More info on the security prices csv requirements can be found at
https://community.quicken.com/discussio ... m-csv-file
emkabat
Posts: 2
Joined: Sat Jul 30, 2022 10:01 am

Re: TSP share prices for Quicken

Post by emkabat »

I've been following this thread for quite some time and find the Python script very valuable. Thanks to all involved in maintaining it.

Hopefully a quick request. The script is returning entries in the resulting CSV file in reverse order, so every time I run it the bottom entry is not the most recent and the script keeps adding previously recorded entries. Can someone supply the code to update the script to sort new entries in date order before appending the new entries to the CSV file? Thanks in advance.
mysteriousclam
Posts: 8
Joined: Fri Jun 03, 2022 4:48 pm

Re: TSP share prices for Quicken

Post by mysteriousclam »

emkabat wrote: Sat Jul 30, 2022 10:08 am I've been following this thread for quite some time and find the Python script very valuable. Thanks to all involved in maintaining it.

Hopefully a quick request. The script is returning entries in the resulting CSV file in reverse order, so every time I run it the bottom entry is not the most recent and the script keeps adding previously recorded entries. Can someone supply the code to update the script to sort new entries in date order before appending the new entries to the CSV file? Thanks in advance.
I just noticed that myself. I adjusted the list splice to compensate.

Code: Select all

#!/usr/bin/python
# script to scrape tsp fund prices from tsp.gov
# adapted from Python 2 script created by user Simbilis on Bogleheads.org
# https://www.bogleheads.org/forum/viewtopic.php?f=1&t=108388

import requests
import csv
from datetime import datetime, timedelta, date
import sys

fundTag = {
    'L Income'  : 'TSPLINCOME',
    'L 2025' : 'TSPL2025',
    'L 2030' : 'TSPL2030',
    'L 2035' : 'TSPL2035',
    'L 2040' : 'TSPL2040',
    'L 2045' : 'TSPL2045',
    'L 2050' : 'TSPL2050',
    'L 2055' : 'TSPL2055',
    'L 2060' : 'TSPL2060',
    'L 2065' : 'TSPL2065',
    'G Fund' : 'TSPGFUND',
    'F Fund' : 'TSPFFUND',
    'C Fund' : 'TSPCFUND',
    'S Fund' : 'TSPSFUND',
    'I Fund' : 'TSPIFUND'}

priceHistoryFile = 'tspQuicken.csv'

lastDate = ''
try:
    quickenReader = csv.reader(open(priceHistoryFile, 'r'))
    lastDate = [row for row in quickenReader][-1][2]
except:
    lastDate = '01/01/2003'

startDate = (datetime.strptime(lastDate, '%m/%d/%Y') + timedelta(1)).strftime('%Y%m%d')
endDate = date.today().strftime('%m/%d/%Y')
if lastDate == endDate:
    print('already have prices through', endDate)
    sys.exit()

print('checking for new prices starting on', startDate)
tspSharePricePageUrl = 'https://www.tsp.gov/data/fund-price-history.csv'
headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.63 Safari/537.36'}

page = requests.get(tspSharePricePageUrl, headers=headers)

reader = csv.reader(page.text.splitlines(), delimiter=',')
rows = [row for row in reader if len(row) > 0]
tagRow = rows[0]
foundNew = False

newRows = []
for row in rows[:1:-1]:
    try:
        currDate = datetime.strptime(row[0], '%Y-%m-%d').strftime('%m/%d/%Y')
    except:
        currDate = '01/01/2002'
    if datetime.strptime(currDate, '%m/%d/%Y') >= datetime.strptime(startDate, '%Y%m%d'):
        for i in range(1, len(row)):
            tag = tagRow[i].lstrip()
            if tag in fundTag:
                try:
                    price = float(row[i])
                except:
                    continue
                newRows.append([fundTag[tag], price, currDate])
                foundNew = True
                print('found', [fundTag[tag], price, currDate])

if foundNew:
    with open(priceHistoryFile, "a", newline='') as file:
        writer = csv.writer(file)
        writer.writerows(newRows)

sys.exit()
User avatar
LadyGeek
Site Admin
Posts: 95686
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: TSP share prices for Quicken

Post by LadyGeek »

This thread is now in the Investing - Theory, News & General forum (general discussion).

New member StalwartBook has created a script for MoneyDance. See: TSP share prices for Moneydance (CSV)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
emkabat
Posts: 2
Joined: Sat Jul 30, 2022 10:01 am

Re: TSP share prices for Quicken

Post by emkabat »

I just noticed that myself. I adjusted the list splice to compensate.
Thanks mysteriousclam.

For some reason the script is not adding the most recent date, the first entry at the top in 'https://www.tsp.gov/data/fund-price-history.csv' I ran the script yesterday(8/3/2022) and today (8/4/2022) and it didn't find or record 8/3/2022 in the tspQuicken CSV file.
impdust
Posts: 22
Joined: Sun Sep 07, 2014 2:41 pm

Re: TSP share prices for Quicken

Post by impdust »

Changing
for row in rows[:1:-1]:
to
for row in rows[:0:-1]:

Code: Select all

#!/usr/bin/python
# script to scrape tsp fund prices from tsp.gov
# adapted from Python 2 script created by user Simbilis on Bogleheads.org
# https://www.bogleheads.org/forum/viewtopic.php?f=1&t=108388

import requests
import csv
from datetime import datetime, timedelta, date
import sys

fundTag = {
    'L Income'  : 'TSPLINCOME',
    'L 2025' : 'TSPL2025',
    'L 2030' : 'TSPL2030',
    'L 2035' : 'TSPL2035',
    'L 2040' : 'TSPL2040',
    'L 2045' : 'TSPL2045',
    'L 2050' : 'TSPL2050',
    'L 2055' : 'TSPL2055',
    'L 2060' : 'TSPL2060',
    'L 2065' : 'TSPL2065',
    'G Fund' : 'TSPGFUND',
    'F Fund' : 'TSPFFUND',
    'C Fund' : 'TSPCFUND',
    'S Fund' : 'TSPSFUND',
    'I Fund' : 'TSPIFUND'}

priceHistoryFile = 'tspQuicken.csv'

lastDate = ''
try:
    quickenReader = csv.reader(open(priceHistoryFile, 'r'))
    lastDate = [row for row in quickenReader][-1][2]
except:
    lastDate = '01/01/2003'

startDate = (datetime.strptime(lastDate, '%m/%d/%Y') + timedelta(1)).strftime('%Y%m%d')
endDate = date.today().strftime('%m/%d/%Y')
if lastDate == endDate:
    print('already have prices through', endDate)
    sys.exit()

print('checking for new prices starting on', startDate)
tspSharePricePageUrl = 'https://www.tsp.gov/data/fund-price-history.csv'
headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.5005.63 Safari/537.36'}

page = requests.get(tspSharePricePageUrl, headers=headers)

reader = csv.reader(page.text.splitlines(), delimiter=',')
rows = [row for row in reader if len(row) > 0]
tagRow = rows[0]
foundNew = False

newRows = []
for row in rows[:0:-1]:
    try:
        currDate = datetime.strptime(row[0], '%Y-%m-%d').strftime('%m/%d/%Y')
    except:
        currDate = '01/01/2002'
    if datetime.strptime(currDate, '%m/%d/%Y') >= datetime.strptime(startDate, '%Y%m%d'):
        for i in range(1, len(row)):
            tag = tagRow[i].lstrip()
            if tag in fundTag:
                try:
                    price = float(row[i])
                except:
                    continue
                newRows.append([fundTag[tag], price, currDate])
                foundNew = True
                print('found', [fundTag[tag], price, currDate])

if foundNew:
    with open(priceHistoryFile, "a", newline='') as file:
        writer = csv.writer(file)
        writer.writerows(newRows)

sys.exit()
mysteriousclam
Posts: 8
Joined: Fri Jun 03, 2022 4:48 pm

Re: TSP share prices for Quicken

Post by mysteriousclam »

impdust wrote: Fri Aug 05, 2022 2:15 pm Changing
for row in rows[:1:-1]:
to
for row in rows[:0:-1]:
thank you impdust! my error on the original...
LuckyLack
Posts: 2
Joined: Fri Sep 30, 2022 2:33 pm

Re: TSP share prices for Quicken

Post by LuckyLack »

Code stopped working for me all of a sudden...

Traceback (most recent call last):
File "C:\Users\josep\Documents\Quicken\TSP.py", line 6, in <module>
import requests
ModuleNotFoundError: No module named 'requests'

I'm a complete python novice, any help is appreciated!
gtd98765
Posts: 952
Joined: Sun Jan 08, 2017 3:15 am

Re: TSP share prices for Quicken

Post by gtd98765 »

The python code just worked for me. Every once in a while it glitches for some reason; I just try to run it again an hour later and it has always worked at that point.
LuckyLack
Posts: 2
Joined: Fri Sep 30, 2022 2:33 pm

Re: TSP share prices for Quicken

Post by LuckyLack »

Seems it's an issue with Thonny 4.0.1/Python 3.10... I downgraded to Thonny 3.3.13/Python 3.7.9 and the script ran just fine.
scoothome
Posts: 99
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

Re: TSP share prices for Quicken

Post by scoothome »

[reviving thread]

If you have been hesitant to use the Python script posted upthread (which works great) and have been manually entering TSP share prices to Quicken once a week or month (with resulting gaps in the data), here is a spreadsheet alternative. You will need to authorize Google Apps Script to use it, instructions are provided.

Image

Here is a link to the Google spreadsheet, make a copy to use: TSP2Quicken

BTW, I'm not sure why Quicken hasn't updated the functionality of "import security prices from CSV file". Currently, the CSV file must have only one security per line, such as:
Security, Price, Date
FundName1, date1fund1Price, date1
FundName2, date1fund2Price, date1
FundName1, date2fund1Price, date2
FundName2, date2fund2Price, date2
...

The fund name can't have any spaces and the header row is ignored.

I don't think it would be that hard for the Quicken team to update the import security prices to allow fund names in the header row and multiple securities per row:
Date, FundName1, FundName2, ...
date1, date1fund1Price, date1fund2Price, ...
date2, date2fund1Price, date2fund2Price, ...
USCJim
Posts: 2
Joined: Tue Jun 14, 2022 7:28 am

Re: TSP share prices for Quicken

Post by USCJim »

Thank you for this! So everything works except the last step which is to import into quicken. So is the idea to save the output under the "TSP2Quicken" tab separately and use that file to import into quicken? I couldn't get it to work but its so close!
scoothome
Posts: 99
Joined: Sun Dec 04, 2022 10:10 pm
Location: California

Re: TSP share prices for Quicken

Post by scoothome »

USCJim wrote: Wed Jun 07, 2023 6:59 am Thank you for this! So everything works except the last step which is to import into quicken. So is the idea to save the output under the "TSP2Quicken" tab separately and use that file to import into quicken? I couldn't get it to work but its so close!
Yes, the script should save the data in the tsp2Quicken tab as a comma-separated-values file named, "tsp2Quicken.csv". On my system (PC), the file gets saved to the default Downloads folder. Can you verify that the tsp2Quicken.csv file was saved to your system? It may have been saved to the folder where you last downloaded a file from your browser. If you can find it, then you should be set for step 4 of the Instructions tab, which is to import the CSV file into Quicken.
impdust
Posts: 22
Joined: Sun Sep 07, 2014 2:41 pm

Re: TSP share prices for Quicken

Post by impdust »

New post to this thread reminded me I should upgrade the version of Python I have installed. When I did that I got the same error LuckyLack mentioned back in September:
File "C:\Users\josep\Documents\Quicken\TSP.py", line 6, in <module>
import requests
ModuleNotFoundError: No module named 'requests'
The fix for that is to install the requests module. For me that was running this command from a command prompt

Code: Select all

python -m pip install requests
Probably too late to help LuckLack, but it might help me remember the next time I upgrade my local install of Python. It's now working fine for me on v3.11.4.
Post Reply