Automating Stocks Watchlist Daily Price Updates: Improving an ugly code and applying styles to an excel file with python

I have been working on a simple script to automate records keeping for daily price changes for stocks in the watchlist. Previously I wrote in What if your code is ugly but it works? Amateur python programmers journey how I was able to finish the project's core logic and was getting functional results, yet code wasn't organized well. This week I was able to make improvements in the organization of the code, fixing bugs, and adding styles to an output excel files so the end result looked good as well. I will share the screenshots for the full code at the end of the post. If you find it useful and would like a text version, I will be glad to share that as well.

Today, I would like talk about the purpose and potential use cases for a simple script like this, go through the logic of the code and how to apply styles to an excel file with python.

What is the purpose of this code?

I wrote it for a personal use to automate manual entry of daily price changes for stocks I am watching for a given week. However, I can see it being used for testing various trading strategies, trading tips from others, and gathering data for making better trading decisions. While in its current form code only works to keep track of price changes of the current week, with simple date change it can also check prices in the past and be useful for backtesting.

One of my next projects will be working on is to automate picking stocks based on various strategies. Combing that with this code will help in seeing how each strategy is performing.

Logic of the code.

As I was trying to better organize the code I put most of the functionality into functions, so it is easier to reuse the functions elsewhere and make changes when needed. Then wrote calls to the functions in the main code section after if __name__ == '__main__':line per pythonic convention.

The main variable user would need to update every week is stocks variable with the list of stocks in the watchlist. Optionally, user can also change the values for strategy, position, and today(date) variables. By default, they can be autofilled. Auto versions of the variables can be commented out and manual entered values uncommented as needed. To generate auto list of text values for strategy and positions, we use auto_list(stocks, text) function to make a list of the same size as number of stocks in the watchlist.

Second function is get_dates(), which takes the date argument by default it is today's date. Then returns a list of dates that we need to get the stock prices for. Since research and preparation for the trading week is mostly done on weekends, the first date we use is Friday for the last and current prices. Depending on the week of the day, function also returns active trading days for the week until today or chosen date.

get_xl_file_path('watchlist.xlsx) returns the absolute path to the excel file where all the data will be stored.

Everything up this point was a preparations of variables that will be used in the core function of the script that will get all the data from Yahoo Finance, open the Excel file, and store gathered data in a worksheet. ***build_xl_file_worksheet() is the core function. Arguments it takes are file_path, stocks, dates, strategy, and position.

After the data is stored in the worksheet, this function returns a name of the updated or created worksheet, so that next we can apply styles to the file and make the worksheet more presentable. That takes us to our last function - apply_styles_xl(). It will need file_path argument and with some new arguments. sheet_name is the name of the worksheet the styles will be applied on and was returned when worksheet data was stored. last_row and date_columns will help with identifying number of rows and columns used in storing data.

Applying styles to an excel file.

Openpyxl module provides us with ability not only to manipulate data in excel files but also to apply and change styles. The last function in the script, ***apply_styles_xl(file_path, sheet_name, last_row, date_columns), opens the excel file we are working with and the worksheet where the data is stored, and goes through all filled cells and apply necessary styles. Following is the list of style changes it applies:

1. Changes font size of the title cell to 24px, aligns values to center, and adds background fill color gray.
2. Changes font size of the header row cells to 20px, aligns values to center, and adds background fill color gray.
3. Changes font size of the Stock names, Strategy, Position cells to 20px, aligns values to center, and adds background color gray.
4. Changes font size of Friday/Main prices column cells to 20px, and adds background color yellow.
5. Goes through each cell in the rest of the price columns, compares the prices to the Friday/Main prices. If price is higher adds background color green, if price is lower or equal adds background color red. Also changes the font size to 20px.
6. Goes through each column with data and adjusts width of the columns based on the longest value in the column, so that everything is visible.

The title cell was also merged among nine columns using sheet.merge_cells("B2:J2") method. This caused a problem when readjusting widths of columns, because title text in "B2" cell was too long and made every cell in column "B" of the same width. To avoid that, I simply added a comparison check to skip "B2" when calculating the width for the rest of the "B" cells. End result looks like the picture below.

Applying styles with openpyxl is straightforward with easy cell object properties like .font, .fill, .alignment, etc and using module classes like Patternfill(), Font(), Alignment(), Border(), etc.

One problem I faced was that I wasn't able to apply styles to a range of cells at the same time. Only option I had was to apply styles to one cell at a time. With python iterations loops it is not big problem. Applying changes to one cell at the time works.

Screenshots of the Code

Posted Using LeoFinance