TSP share prices for Quicken
Re: TSP share prices for Quicken
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?
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?
Re: TSP share prices for Quicken
Perfect! Appreciate it.jsprag wrote: ↑Sun Feb 23, 2020 3:51 amSee if this works for you in Python 3.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.
Re: TSP share prices for Quicken
Script updated to reflect 1 July changes to TSP funds:jsprag wrote: ↑Sun Feb 23, 2020 3:51 amSee if this works for you in Python 3.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.
- 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.
Re: TSP share prices for Quicken
Re: TSP share prices for Quicken
Thank you both. I'm sure I'm not the only one using this, and it's greatly appreciated.
Re: TSP share prices for Quicken
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!
But in general, thanks so much for creating and maintaining this tool!
Re: TSP share prices for Quicken
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! It was never a good idea to get this data from some rando on the Internet anyway
Re: TSP share prices for Quicken
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.
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.
Re: TSP share prices for Quicken
Ok, fixt. Please retry. It's been so long, I forgot the problem that was being solved
Thanks @impdust for the new URL.
Thanks @impdust for the new URL.
Re: TSP share prices for Quicken
It now works for me. Thanks again!
Re: TSP share prices for Quicken
Thanks @Simbilis for continuing to support it. You're the best "rando on the Internet"!
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.
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.
Re: TSP share prices for Quicken
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!
Good luck, folks!
Re: TSP share prices for Quicken
@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.
-
- Posts: 8
- Joined: Fri Jun 03, 2022 4:48 pm
Re: TSP share prices for Quicken
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!
@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.
Re: TSP share prices for Quicken
@impdust @mysteriousclam
Re: TSP share prices for Quicken
Thanks mysteriousclam!
-
- Posts: 2
- Joined: Wed Jun 08, 2022 10:19 am
Re: TSP share prices for Quicken
Thank you @mysteriousclam
I'm curious on how you found that new URL of https://www.tsp.gov/data/getSharePrices ... oad_0.html
I'm curious on how you found that new URL of https://www.tsp.gov/data/getSharePrices ... oad_0.html
-
- Posts: 8
- Joined: Fri Jun 03, 2022 4:48 pm
Re: TSP share prices for Quicken
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 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
-
- Posts: 2
- Joined: Wed Jun 08, 2022 10:19 am
Re: TSP share prices for Quicken
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.mysteriousclam wrote: ↑Wed Jun 08, 2022 7:40 pmI 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 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
Re: TSP share prices for Quicken
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!
Re: TSP share prices for Quicken
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.
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.
-
- Posts: 8
- Joined: Fri Jun 03, 2022 4:48 pm
Re: TSP share prices for Quicken
It should be as easy as follows (OS agnostic):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!
- Download Python 3.
- Create a new text file with a '.py' extension that consists of the code I included in my original post.
- Run the file you created. On Windows, you should be able to double click on the file and run it.
-
- Posts: 1
- Joined: Sat Jun 25, 2022 2:02 pm
Re: TSP share prices for Quicken
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.
Re: TSP share prices for Quicken
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.
Re: TSP share prices for Quicken
Also not a Python expert. But try this: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.
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.
Re: TSP share prices for Quicken
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.
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.
Re: TSP share prices for Quicken
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.
-
- Posts: 8
- Joined: Fri Jun 03, 2022 4:48 pm
Re: TSP share prices for Quicken
Looks like TSP changed their files a bit, so the old code won't work. The URL needs to be replaced with
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
https://www.tsp.gov/data/getSharePricesRaw_startdate_20220424_enddate_20220524_Lfunds_1_InvFunds_1_download_0.html
Code: Select all
https://www.tsp.gov/data/getSharePricesRaw_startdate_20220424_enddate_20220524_Lfunds_1_InvFunds_1_download_0.csv
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()
Re: TSP share prices for Quicken
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
-
- Posts: 8
- Joined: Fri Jun 03, 2022 4:48 pm
Re: TSP share prices for Quicken
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'}
Re: TSP share prices for Quicken
Thank you mysteriousclam, that did it. I wonder how I managed to delete that line?
-
- Posts: 8
- Joined: Fri Jun 03, 2022 4:48 pm
Re: TSP share prices for Quicken
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()
Re: TSP share prices for Quicken
If you paste that url into the browser (Chrome), it downloads the history in csv format. So do you really need the python script?
Re: TSP share prices for Quicken
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.
Re: TSP share prices for Quicken
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
Re: TSP share prices for Quicken
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.
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.
-
- Posts: 8
- Joined: Fri Jun 03, 2022 4:48 pm
Re: TSP share prices for Quicken
I just noticed that myself. I adjusted the list splice to compensate.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.
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()
Re: TSP share prices for Quicken
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)
New member StalwartBook has created a script for MoneyDance. See: TSP share prices for Moneydance (CSV)
Re: TSP share prices for Quicken
Thanks mysteriousclam.I just noticed that myself. I adjusted the list splice to compensate.
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.
Re: TSP share prices for Quicken
Changing
for row in rows[:1:-1]:
to
for row in rows[:0:-1]:
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()
-
- Posts: 8
- Joined: Fri Jun 03, 2022 4:48 pm
Re: TSP share prices for Quicken
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!
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!
Re: TSP share prices for Quicken
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.
Re: TSP share prices for Quicken
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.
Re: TSP share prices for Quicken
[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.
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, ...
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.
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, ...
Re: TSP share prices for Quicken
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!
Re: TSP share prices for Quicken
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.
Re: TSP share prices for Quicken
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:
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.
The fix for that is to install the requests module. For me that was running this command from a command promptFile "C:\Users\josep\Documents\Quicken\TSP.py", line 6, in <module>
import requests
ModuleNotFoundError: No module named 'requests'
Code: Select all
python -m pip install requests