Last week I briefly wrote about automating Stocks data retrieval from Yahoo Finance and storing them in an Excel spreadsheet. The goal is to replace manual look up of stock prices and manually entering them into the spreadsheet. The purpose of the spreadsheet is to keep record of price changes throughout the week for the Stocks I am watching and planning to trade.
To accomplish this goal I split up the project into 3 steps. First step of the project fetches the Stocks Historical Data with a python code from Yahoo Finance. This step has been accomplished. I shared the code in my Amateur Trader's Diary - Tuesday Updates & Automating with Python post.
Second step is to create a spreadsheet or update and existing one with a template and is the subject of this post. Third step will be to combine both codes and enter Stocks price data into the spreadsheet and apply some styles.
Following is the code that helps with creating a template and saves needed data in Excel file.
Let's walk through the code line by line:
import openpyxl
import os
from openpyxl.utils import get_column_letter
Here were are importing dependancies we will need. Main was in openpyxl. This will allow us to interact with an Excel file. We can get away without using os if we type in an absolute path for the file we will be working with. For some reason I got an error using a relative path, just typing in the file name. Using an absolute path worked. Last dependency get_column_letter will help us convert numbers to column names like A, B, C, etc. so it is easy to iterate with a for loop.
diary_path = os.getcwd() + '/Desktop/'
diary_file = 'trading_diary.xlsx'
I am saving the path to the file and the file name in separate variables, so it is easier to reuse the code for different projects with simply changing the file name. os.getcwd() will return current working directory in as a string.
wb = openpyxl.load_workbook(diary_path + diary_file)
This code will open our Excel file and store the workbook in variable wb, so we can read from it or write data.
week = 'Week 2'
stocks = ['MRNA','DS','PTON','FB','AMD','NKLA','IOVA','CASA','NVTA','IDEX']
header_row= ['Stocks', 'TimeFrame', 'Position', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']
week variable will represent the worksheet we will be updating. stocks represent this weeks stock picks, and header_row is a title for each column. At the beginning of the week I will manually change the variables week, and stocks. header_row will remain same for all weeks, unless I decide to change the days of the week to actual date. Actual dates probably would be better.
if week not in wb.sheetnames:
wb.create_sheet(week)
This code will check if 'Week 2' sheet exists in the workbook. If it doesn't it will create a worksheet with the name 'Week 2' or whatever the value is for week variable.
sheet = wb[week]
row = 1
col = 1
for i in header_row:
col_letter = get_column_letter(col)
cell = col_letter + str(row)
sheet[cell] = i
col += 1
Now, I can assign the proper worksheet to a sheet variable and save data in header_row in the that worksheet. This is where get_column_letter() is being helpful converting numbers to alphabetic representation of the column names.
row = 2
col = 1
for j in stocks:
col_letter = get_column_letter(col)
cell = col_letter + str(row)
sheet[cell] = j
row += 1
Here we are entering Stock names under the header row and in the first column in a similar fashion as we did with previous for loop.
wb.save(diary_path+diary_file)
Lastly, after everything we need to do with the worksheet is done we need to save the changes.
Simple code, yet saves a lot of time. That is why I like python. Next time I hope to combine both codes, and complete the project with applying some more logic and styles to the Excel spreadsheet.