Saving Stocks Data in Microsoft Excel using Python

avatar

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.

automate_2.png

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.

Posted Using LeoFinance



0
0
0.000
5 comments
avatar

Hello @geekgirl. Thanks for illustrating out all this process. This is the kind of publication we appreciate in our community. Do endeavor to check us out.

Your post has been curated with @gitplait community account because this is the kind of publications we like to see in our community.

Join our Community on Hive and Chat with us on Discord.

[Gitplait-Team]

0
0
0.000
avatar

Congratulations @geekgirl! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :

You received more than 70000 upvotes. Your next target is to reach 75000 upvotes.

You can view your badges on your board And compare to others on the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

To support your work, I also upvoted your post!

Support the HiveBuzz project. Vote for our proposal!
0
0
0.000
avatar

This is the kind of really useful project / information / exemplar that I love! Thanks!

I'm making some space in my life to get back into Python after a few years away. Examples like this always cheer me up and make me feel like yes, it might be within my grasp to do this :-)

0
0
0.000
avatar

Thank You. Python is fun. I like it because one doesn’t have to be a programmer to use it, to automate daily tasks.

0
0
0.000
avatar

I have picked your post for my daily hive voting initiative, Keep it up and Hive On!!

0
0
0.000