What will we cover in this tutorial?
We will learn how you can dynamically format the cells in Excel to have a specific color based on the value of a function – all done from Python.
As an example we will color the cells of two columns depending on which is larger.
In this concrete example we will collect historical stock prices and calculate the Moving Averages of 50 and 200 days on the closing price. In a crossover way to utilize moving averages, you would send a buy-signal when the 50-day moving average crosses above the 200-day moving average and a sell-signal if the other way around.
Step 1: Collect the data from Yahoo! Finance using Pandas-datareader
A great library to use to collect financial data is the Pandas-datareader.
We will collect data from Yahoo! Finance. As an example we will use General Motors historical stock data.
This can be done as follows.
import pandas_datareader as pdr
import datetime as dt
start = dt.datetime(2019, 1, 1)
ticker = pdr.get_data_yahoo("GM", start)
This will result in a Pandas DataFrame with data similar to this.
High Low Open Close Volume Adj Close
Date
2019-01-02 33.810001 32.410000 32.849998 33.639999 7662300.0 31.893360
2019-01-03 33.680000 32.200001 33.340000 32.250000 11618300.0 30.575533
2019-01-04 33.570000 32.430000 32.799999 33.330002 10784000.0 31.599455
2019-01-07 34.970001 33.480000 33.709999 34.360001 10722300.0 32.575977
2019-01-08 35.680000 34.490002 35.000000 34.810001 10532900.0 33.002617
Step 2: Calculate the Moving Averages
The moving average can be calculated by applying the rolling() and mean() methods on the DataFrame.
The rolling() creates a window size which is the number of observations used for calculating the statistic.
To calculate the Moving Average of window size 50 will use the last 50 data points to calculate the mean. To be consistent with Yahoo! Finance way of calculate the value, they use the Close price.
import pandas_datareader as pdr
import datetime as dt
import xlsxwriter
import pandas as pd
start = dt.datetime(2019, 1, 1)
ticker = pdr.get_data_yahoo("GM", start)
ticker['MA50'] = ticker['Close'].rolling(50).mean()
ticker['MA200'] = ticker['Close'].rolling(200).mean()
Which will give a tail similar to this output.
High Low Open ... Adj Close MA50 MA200
Date ...
2020-12-14 42.540001 41.419998 42.490002 ... 41.619999 38.5632 29.03155
2020-12-15 42.160000 41.310001 41.990002 ... 41.660000 38.7772 29.08725
2020-12-16 41.869999 40.810001 41.790001 ... 41.419998 38.9976 29.13670
2020-12-17 42.029999 41.430000 41.709999 ... 42.029999 39.2058 29.19635
2020-12-18 42.042801 41.139999 42.020000 ... 41.389999 39.3894 29.25985
Where the Moving Averages are added to the DataFrame.
Step 3: Exporting to Excel using XlsxWriter
We will use the powerful XlsxWriter to create our Excel sheet.
You can write the DataFrame directly by using to_excel(…), but we want to do more than that. Hence, we use the XlsxWriter directly.
import pandas_datareader as pdr
import datetime as dt
import pandas as pd
start = dt.datetime(2019, 1, 1)
ticker = pdr.get_data_yahoo("GM", start)
ticker['MA50'] = ticker['Close'].rolling(50).mean()
ticker['MA200'] = ticker['Close'].rolling(200).mean()
# We only want the index as a date and not datetime object
ticker.index = ticker.index.date
# We skip the first 200 entries, as it they do not have the MA200
ticker = ticker.iloc[200:]
# We reverse the DataFrame to have newest first
ticker = ticker.iloc[::-1]
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('moving_average.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
ticker.to_excel(writer, sheet_name='Moving Averages')
# Remember to close the writer to write the Excel sheet
writer.close()
This will create an Excel sheet similar to this one.
Step 4: Adding formatting to the cells
This is a bit more complex as we need to do the magic here.
We first need to create a reference to the sheet (worksheet) we work on to access the functionality.
The first thing we do is to resize column A, such that it opens in the correct size to see the date. This is done by using set_column(…).
Then we create a format, which can be applied on Excel cells. This is how we color them. Hence, we create one for green cells and later one for red cells.
We insert them by using conditional_format(…), which a range of cells. This can be applied in different ways, we use the A1 notation.
The options parameter in conditional_format(…) must be a dictionary containing the parameters that describe the type and style of the conditional format. To see details of the format please refer to the manual.
import pandas_datareader as pdr
import datetime as dt
import pandas as pd
start = dt.datetime(2019, 1, 1)
ticker = pdr.get_data_yahoo("GM", start)
ticker['MA50'] = ticker['Close'].rolling(50).mean()
ticker['MA200'] = ticker['Close'].rolling(200).mean()
# We only want the index as a date and not datetime object
ticker.index = ticker.index.date
# We skip the first 200 entries, as it they do not have the MA200
ticker = ticker.iloc[200:]
# We reverse the DataFrame to have newest first
ticker = ticker.iloc[::-1]
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('moving_average.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
ticker.to_excel(writer, sheet_name='Moving Averages')
# Get the xlsxwriter objects from the dataframe writer object.
workbook = writer.book
worksheet = writer.sheets['Moving Averages']
# Resize the column A
worksheet.set_column("A:A", 12)
# Calculate the last row number (we insert first DataFrame row in row 2)
last_row = len(ticker) + 1
# Create a format for a green cell
cell_format_green = workbook.add_format()
cell_format_green.set_bg_color('green')
# Create a conditional formatted of type formula
worksheet.conditional_format('H2:I{}'.format(last_row), {'type': 'formula',
'criteria': '=$H2>=$I2',
'format': cell_format_green})
# Create a format for a red cell
cell_format_red = workbook.add_format()
cell_format_red.set_bg_color('red')
# Create a conditional formatted of type formula
worksheet.conditional_format('H2:I{}'.format(last_row), {'type': 'formula',
'criteria': '=$H2<$I2',
'format': cell_format_red})
# Remember to close the writer
writer.close()
This will result in the expected Excel sheet.
Step 5: Validating it works as expected
Now for the final test.
The colors should be updated dynamically. Let’s try to change the value and see what happens.
It does. If we change the value of H2 to 10, it turns the cells to red.
Python for Finance: Unlock Financial Freedom and Build Your Dream Life
Discover the key to financial freedom and secure your dream life with Python for Finance!
Say goodbye to financial anxiety and embrace a future filled with confidence and success. If you’re tired of struggling to pay bills and longing for a life of leisure, it’s time to take action.
Imagine breaking free from that dead-end job and opening doors to endless opportunities. With Python for Finance, you can acquire the invaluable skill of financial analysis that will revolutionize your life.
Make informed investment decisions, unlock the secrets of business financial performance, and maximize your money like never before. Gain the knowledge sought after by companies worldwide and become an indispensable asset in today’s competitive market.
Don’t let your dreams slip away. Master Python for Finance and pave your way to a profitable and fulfilling career. Start building the future you deserve today!
Python for Finance a 21 hours course that teaches investing with Python.
Learn pandas, NumPy, Matplotlib for Financial Analysis & learn how to Automate Value Investing.
“Excellent course for anyone trying to learn coding and investing.” – Lorenzo B.
