Scripts: How I partially automated my finance tracking system

I am currently studying computer science. One of my interests is in using my computer science knowledge to aid me in my personal finance and investing journey. So I wondered since I update my finances every week, why not automate a portion of my finance tracking system?

So I decided to write a script that could update my Excel spreadsheet with current prices.

What I used:

• Python - I decided to use Python as I have done several scripts in Python. It is easy to use and has the packages I need to get started
• openpyxl - a Python module that can edit Excel sheets.
• cronjob - I wrote a cronjob to automate the running of the script.

Portfolio set up:

1. Open an Excel sheet to create your portfolio. It's better if you already have your own portfolio in Excel. 

2. Go to Yahoo Finance and search for the stocks you want to include in your portfolio. Copy the URL into a row in your portfolio. 

Python set up:

1. If you don't already have Python installed, head over to python.org to install Python. 

2. Install pip on your computer. Pip is a Python Package Installer which will make installing our required packages easy. 

3. We need to install these packages: bs4, openpyxl, urllib2

4. Go to the command line and type in these commands, one after another:

pip install openpyxl
pip install bs4
pip install urllib2

Retrieving prices from Yahoo Finance:

Now that we have the prerequisites, let's start coding! Our first step is to create a function:

retrieve_price(url)

that retrieves prices from a given URL.

Start by importing the relevant modules:

• bs4 is a Python module that allows us to web scrape websites. 
• urllib2 is a module that allows us to read from a URL.
• openpyxl is a Python module that allows us to edit Excel sheets.

Import these packages into our script:

import openpyxl
from bs4 import BeautifulSoup
import urllib2

def retrieve_price(url):
    //code here

In our retrieve_price function, we need to retrieve the contents of a web page and get the stock price from the contents. To do so, make use of the urllib2 module to read from a script: 

content = urllib2.urlopen(url).read() 

This creates a BeautifulSoup object out of the contents of a webpage. 

soup = BeautifulSoup(content, 'html.parser') 

To find the stock price, we have to look through the HTML script and find the code that represents the stock price. In the Yahoo Finance page, move your cursor to the price, right-click and click 'Inspect'. The price is tagged under 'span' tags, in the <insert class here>. Based on our BeautifulSoup object, we can find information relating to that tag:

price = soup.find('span', {'class', 'Trsdu(0.3)'}).string

But at this point, price is a string and not a number that we want. So we need to convert it to the type we want:

price = float(price)

After we have the price, return it from the function. Putting it all together, this is how our script should look like now:

import openpyxl
from bs4 import BeautifulSoup
import urllib2

def retrieve_price(url):
 content = urllib2.urlopen(url).read()
 soup = BeautifulSoup(content, 'html.parser')
 #get this from original webscraping script
 price = soup.find('span', {'class':'Trsdu(0.3s)'}).string
 price = float(price) #convert from unicode to string
 return price

Opening and Editing portfolio in Excel:

1. Get the path of your Excel stock portfolio. 

2. Opening a workbook in our script is similar to opening an Excel file. To open a workbook in our script, we use the openpyxl module. Open the excel file:

savings_workbook = openpyxl.load_workbook('NAME_OF_EXCEL_FILE')

3. Replace 'EXCEL_PORTFOLIO' with the path to your excel portfolio.

4. In a workbook, there are worksheets that we can edit. This is similar to how when we edit an Excel file, we can create and edit many worksheets. To access a particular workbook:

portfolio = savings_workbook.get_sheet_by_name('Portfolio 2019')

5. The goal is to: read the URL from the excel sheet, scrape the current stock price, then update a specific cell in the excel workbook with the price. For this tutorial, I will paste the Yahoo Finance link of the stock to cell B2, and assign cell G2 to where the stock prices will be updated.

6. To access the URL from cell B2 [column = 2, row = 2], we have to access a cell from our Worksheet:

start_column = 2

start_row = 2

current_cell = portfolio.cell(row=start_row, column=start_column)

7. To access the URL from the cell:

url = current_cell.value

8. To update the stock prices for all the stocks, include a loop to access the URLs for all the stocks in the Excel sheet.

current_cell = portfolio.cell(row=start_row, column=start_column)
 while current_cell.value is not None:
  if current_cell.value != 'SOLD':
   url = current_cell.value
   #go to url to retrieve price
   price = retrieve_price(url)
   #update stock price value
   portfolio.cell(row=start_row+2, column=start_column+5, value=price)


  start_row += 4
  current_cell = portfolio.cell(row=start_row, column=start_column)

9. After this, we need to save the workbook:

savings_workbook.save('NAME_OF_EXCEL_FILE') 

Scheduling a cronjob

Now that our script is completed, we have to schedule the script to run periodically to update our portfolio automatically. We can schedule a cronjob for Mac or Linux computers. Windows users can use the Task Scheduler.

This cronjob I scheduled runs the Python script at every Saturday, at 12pm:

0 12 * * 6 python <link to python script> 

This is a good link to learn how to schedule a cronjob: https://code.tutsplus.com/tutorials/scheduling-tasks-with-cron-jobs--net-8800

Conclusion

Now you have learned how to create a stock portfolio in Excel and to retrieve the stock prices automatically. To look at the full code, visit this link: https://github.com/C-likethis123/Portfolio-Update-Script/tree/master

You can also add on to the script provided, for example by including a function to automatically retrieve dividend data. Happy coding!