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:
Now that we have the prerequisites, let's start coding! Our first step is to create a function:
Start by importing the relevant modules:
Import these packages into our script:
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:
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:
6. To access the URL from cell B2 [column = 2, row = 2], we have to access a cell from our Worksheet:
7. To access the URL from the cell:
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.
9. After this, we need to save the workbook:
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:
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!
You can also add on to the script provided, for example by including a function to automatically retrieve dividend data. Happy coding!
