Learn how you can become a Python programmer in just 12 weeks.

    We respect your privacy. Unsubscribe at anytime.

    Excel Automation with Simple Moving Average from Python

    What will we cover in this tutorial?

    We will retrieve the historic stock prices and calculate the moving average. Then we will export the data to Excel and insert a chart, but all done from Python.

    See the in depth explanation in the YouTube video. It also gives advice on how to interpret the Simple Moving Averages (SMA).

    Watch tutorial

    Step 1: Read historic stock prices

    We will use the Pandas-datarader to get the historic prices of NFLX (the ticker for Netflix).

    import pandas_datareader as pdr
    import datetime as dt
    ticker = "NFLX"
    start = dt.datetime(2019, 1, 1)
    data = pdr.get_data_yahoo(ticker, start)
    print(data.head())
    

    And you will get the historic data for Netflix from January 1st, 2019.

    	High	Low	Open	Close	Volume	Adj Close
    Date						
    2019-01-02	269.750000	256.579987	259.279999	267.660004	11679500	267.660004
    2019-01-03	275.790009	264.429993	270.200012	271.200012	14969600	271.200012
    2019-01-04	297.799988	278.540009	281.880005	297.570007	19330100	297.570007
    2019-01-07	316.799988	301.649994	302.100006	315.339996	18620100	315.339996
    2019-01-08	320.589996	308.010010	319.980011	320.269989	15359200	320.269989
    

    Step 2: Understand Moving Average

    We will calculate the Simple Moving Average as defined on Investopedia.

    Simple Moving Average

    The Simple Moving Average (Now just referred to as Moving Average or MA) is defined by a period of days.

    That is, the MA of a period of 10 (MA10) will take the average value of the last 10 close prices. This is done in a rolling way, hence, we will get a MA10 for every trading day in our historic data, except the first 9 days in our dataset.

    We can similarly calculate a MA50 and MA200, which is a Moving Average of the last 50 and 200 days, respectively.

    Step 3: Calculating the Moving Averages

    We can do that by using rolling and mean.

    And it is magic.

    data['MA10'] = data['Close'].rolling(10).mean()
    data['MA50'] = data['Close'].rolling(50).mean()
    data['MA200'] = data['Close'].rolling(200).mean()
    print(data.tail())
    

    That was easy, right?

    	High	Low	Open	Close	Volume	Adj Close	MA10	MA50	MA200
    Date									
    2021-01-12	501.089996	485.670013	500.000000	494.250000	5990400	494.250000	515.297998	502.918599	477.08175
    2021-01-13	512.349976	493.010010	495.500000	507.790009	5032100	507.790009	512.989999	503.559600	477.76590
    2021-01-14	514.500000	499.579987	507.350006	500.859985	4177400	500.859985	510.616995	503.894399	478.39270
    2021-01-15	506.320007	495.100006	500.000000	497.980011	5890200	497.980011	506.341998	504.109600	479.06220
    2021-01-19	509.250000	493.540009	501.000000	501.769989	11996900	501.769989	504.232999	504.205999	479.72065
    

    Step 4: Visualize it with Matplotlib

    We can see the data with Matplotlib.

    import matplotlib.pyplot as plt
    data[['Close', 'MA10', 'MA50']].loc['2020-01-01':].plot()
    plt.show()
    

    Resulting in the following plot.

    The output

    Where you can see how the MA10 and MA50 move according to the price.

    Step 5: Export to Excel

    Now we will export the data to Excel.

    For this we need to import Pandas and use the XlsxWriter engine, where you can find the details of the code.

    The code can be found here.

    import pandas as pd
    data = data.loc['2020-01-01':]
    data = data.iloc[::-1]
    writer = pd.ExcelWriter("technical.xlsx", 
                            engine='xlsxwriter', 
                            date_format = 'yyyy-mm-dd', 
                            datetime_format='yyyy-mm-dd')
    sheet_name = 'Moving Average'
    data[['Close', 'MA10', 'MA50']].to_excel(writer, sheet_name=sheet_name)
    
    worksheet = writer.sheets[sheet_name]
    workbook = writer.book
    # Create a format for a green cell
    green_cell = workbook.add_format({
        'bg_color': '#C6EFCE',
        'font_color': '#006100'
    })
    # Create a format for a red cell
    red_cell = workbook.add_format({
        'bg_color': '#FFC7CE',                            
        'font_color': '#9C0006'
    })
    
    # Set column width of Date
    worksheet.set_column(0, 0, 15)
    
    for col in range(1, 4):
        # Create a conditional formatted of type formula
        worksheet.conditional_format(1, col, len(data), col, {
            'type': 'formula',                                    
            'criteria': '=C2>=D2',
            'format': green_cell
        })
        # Create a conditional formatted of type formula
        worksheet.conditional_format(1, col, len(data), col, {
            'type': 'formula',                                    
            'criteria': '=C2<D2',
            'format': red_cell
        })
    # Create a new chart object.
    chart1 = workbook.add_chart({'type': 'line'})
    # Add a series to the chart.
    chart1.add_series({
            'name': "MA10",
            'categories': [sheet_name, 1, 0, len(data), 0],
            'values': [sheet_name, 1, 2, len(data), 2],
    })
    # Create a new chart object.
    chart2 = workbook.add_chart({'type': 'line'})
    # Add a series to the chart.
    chart2.add_series({
            'name': 'MA50',
            'categories': [sheet_name, 1, 0, len(data), 0],
            'values': [sheet_name, 1, 3, len(data), 3],
    })
    # Combine and insert title, axis names
    chart1.combine(chart2)
    chart1.set_title({'name': sheet_name + " " + ticker})
    chart1.set_x_axis({'name': 'Date'})
    chart1.set_y_axis({'name': 'Price'})
    # Insert the chart into the worksheet.
    worksheet.insert_chart('F2', chart1)
    writer.close()
    

    Where the output will be something similar to this.

    Generated Excel sheet

    12% Investment Solution

    Would you like to get 12% in return of your investments?

    D. A. Carter promises and shows how his simple investment strategy will deliver that in the book The 12% Solution. The book shows how to test this statement by using backtesting.

    Did Carter find a strategy that will consistently beat the market?

    Actually, it is not that hard to use Python to validate his calculations. But we can do better than that. If you want to work smarter than traditional investors then continue to read here.

    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.

    Leave a Comment