Blog‎ > ‎

Python: Scripting TSP data import to Quicken

posted May 11, 2016, 11:41 AM by Christopher Furton   [ updated Sep 22, 2017, 3:40 PM ]
Written by: Christopher Furton

One great thing about being a geek is having the ability to automate those tasks that you simply just don't want to do.  This post is a perfect example of that.  Every month, I import data from the Thrift Savings Plan (TSP) into Quicken, but sadly, the TSP doesn't have the ability to download the data in a format that can be imported into Quicken.  To do this, I would spend about 5 minutes per month retrieving share prices from the TSP website, modifying the format, and then importing it into Quicken.  Sounds like a good opportunity to automate.  I haven't coded in about 10 years, so I figured I would give it a shot using Python.  About 8 hours later, I had all the needed applications installed and the code below accomplishing the task.  

*** Don't forget to "pip install requests" !!

If time permits, I hope to build a GUI for this and change out some of the hard-coded information for something more dynamic.  In the meantime, here it is:

## Create CSV file for from TSP.Gov website for import into Quicken 2015
from lxml import html
import requests
import datetime

# Function to retrieve data from TSP website 
def retrieveData():
   page = requests.get('https://www.tsp.gov/InvestmentFunds/FundPerformance/index.html')
   tree = html.fromstring(page.content)
   dates = tree.xpath('//td[@class="leadingCell"]/text()')
   values = tree.xpath('//td[@class="packed"]/text()')
   funds = tree.xpath('//th[@class="packed"]/text()')
   return(dates,values,funds);
   
# Function to parse data and then export to file
def parseData(dates,values,funds):
   #Change dates from 'Apr 10, 2016' to '4/10/2016
   x=0
   while(x < len(dates)):
      date = dates[x]
      dates[x] = datetime.datetime.strptime(date,'%b %d, %Y').strftime('%m/%d/%y')
      x += 1 
 
   #Change fund names to match Quicken Ticker Symbol
   x=0
   while(x < len(funds)):
      fund = funds[x]
      if fund == 'L Income':
         funds[x]='TSPLIncome'
      elif fund == 'L 2020':
         funds[x]='TSPL2020'
      elif fund == 'L 2030':
         funds[x]='TSPL2030'
      elif fund == 'L 2040':
         funds[x]='TSPL2040'
      elif fund == 'L 2050':
         funds[x]='TSPL2050'
      elif fund == 'G Fund':
         funds[x]='TSPGFund'
      elif fund == 'F Fund':
         funds[x]='TSPFFund'
      elif fund == 'C Fund':
         funds[x]='TSPCFund'
      elif fund == 'S Fund':
         funds[x]='TSPSFund'
      elif fund == 'I Fund':
         funds[x]='TSPIFund'         
      x += 1
   
   #remove excess spaces and /n from share values
   x=0
   while(x < len(values)):
      temp = values[x]
      values[x] = temp[4:11]
      x += 1
   
   #Format into CSV rows and write to file
   fo = open("exportToQuicken.txt", "w")
   itemNum=0
   for date in dates:
      colNum=0
      while(colNum < len(funds)):
         fo.write(funds[colNum] + "," + date + "," + values[itemNum] + "\n")
         itemNum += 1
         colNum += 1 
   fo.close()
   return;

def main():
   dates, values, funds = retrieveData()
   parseData(dates,values,funds)
   return;

main()



About the Author

Christopher Furton author bio picture
Christopher Furton

is an Information Technology Professional with over 12 years in the industry.  He attended The University of Michigan earning a B.S. in Computer Science and recently completed a M.S. in Information Management from Syracuse University.  His career includes managing small to medium size IT infrastructures, service desks, and IT operations.  Over the years, Christopher has specialized in Cyber Security while working within the Department of the Defense and the United States Marine Corps. His research topics include vulnerability management, cyber security governance, privacy, and cyber risk management.  He holds active IT Certifications including the CISSP, CEH, ITIL Foundations, Security+CE and Network+CE.  He can be found on , , and .  

Additional information available on Christopher Furton's website at
Comments