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

    We respect your privacy. Unsubscribe at anytime.

    Create Excel Sheet with Stock Prices and Moving Average with Chart all from Python

    What will we cover in this tutorial?

    In this tutorial we will read a historic stock prices, calculate the moving average and export that to an Excel sheet and insert a chart with prices and moving average. And all will be done from Python using Pandas and Pandas-datareader.

    Step 1: Get the historic stock prices

    A great place to get historic stock prices is using Pandas-datareader. It has a great interface to various places to get the data. In this tutorial we will use Yahoo API through the Pandas-datareader.

    It doesn’t require any registration to use the API. It works straight out of the box.

    To get stock prices in time series you need to find the ticker of your favorite stock. In this tutorial we will use Apple, which has ticker AAPL.

    import pandas_datareader as pdr
    import datetime as dt
    start = dt.datetime(2020, 1, 1)
    ticker = pdr.get_data_yahoo("AAPL", start)

    You also need to set the date to set how far back you want historic stock prices. By default, you will get up to the most current date. Also, you will data for each day open. You can use more arguments to configure what if you want weekly or monthly prices. Also, you can set a end-date, if you like.

    The above code should give output similar to the following.

                     High        Low       Open      Close       Volume  Adj Close
    2020-01-02  75.150002  73.797501  74.059998  75.087502  135480400.0  73.840042
    2020-01-03  75.144997  74.125000  74.287498  74.357498  146322800.0  73.122154
    2020-01-06  74.989998  73.187500  73.447502  74.949997  118387200.0  73.704819
    2020-01-07  75.224998  74.370003  74.959999  74.597504  108872000.0  73.358185
    2020-01-08  76.110001  74.290001  74.290001  75.797501  132079200.0  74.538239

    Step 2: Calculate the Moving Average

    To calculate the moving average (also called the simple moving average), we can use the rolling method on a DataFrame.

    The rolling method takes one argument, which is the window size. This indicates how big a window we want to apply a function on. In this case we want to apply the mean function on a window of size 50.

    import pandas_datareader as pdr
    import pandas as pd
    import datetime as dt
    start = dt.datetime(2020, 1, 1)
    ticker = pdr.get_data_yahoo("AAPL", start)
    ticker['MA'] = ticker['Close'].rolling(50).mean()

    This calculates the simple moving average of window size 50. This will give the same result as the moving average with the default window of 50 will give in Yahoo! Finance.

    The output will be as follows.

                     High        Low       Open  ...       Volume  Adj Close        MA
    Date                                         ...                                  
    2020-03-16  64.769997  60.000000  60.487499  ...  322423600.0  59.687832  76.16100
    2020-03-17  64.402496  59.599998  61.877499  ...  324056000.0  62.312309  75.93815
    2020-03-18  62.500000  59.279999  59.942501  ...  300233600.0  60.786911  75.67250
    2020-03-19  63.209999  60.652500  61.847500  ...  271857200.0  60.321156  75.40445
    2020-03-20  62.957500  57.000000  61.794998  ...  401693200.0  56.491634  75.03470

    Notice we removed the first 50 rows (actually 51, as we index from 0). This is because the MA (moving average) column will not have numbers before we reach here.

    Step 3: Export data to Excel and create a chart with close prices and moving average

    Now this is where it all get’s a bit more complicated. It takes some reading in the manual to figure all this out.

    The code is commented to explain what happens.

    import pandas_datareader as pdr
    import pandas as pd
    import datetime as dt
    # Read the stock prices from Yahoo! Finance
    start = dt.datetime(2020, 1, 1)
    ticker = pdr.get_data_yahoo("AAPL", start)
    # Calculate the moving average with window size 50
    ticker['MA'] = ticker['Close'].rolling(50).mean()
    # Create a Pandas Excel writer using XlsxWriter as the engine.
    writer = pd.ExcelWriter('apple.xlsx', engine='xlsxwriter')
    # Name the sheet
    sheet_name = "Apple"
    # We convert the index from datetime to date
    # This makes the data in Excel only have the date and
    # not the date with time: 00:00:00:0000
    ticker.index = ticker.index.date
    # Skip the first 51 rows (to be pedantic, 49 is the correct number)
    ticker = ticker[50:]
    # Convert the dataframe to an XlsxWriter Excel object.
    ticker.to_excel(writer, sheet_name=sheet_name)
    # Get the xlsxwriter workbook and worksheet objects.
    workbook = writer.book
    worksheet = writer.sheets[sheet_name]
    # Widen the first column to display the dates.
    worksheet.set_column('A:A', 12)
    # Get the number of rows and column index
    max_row = len(ticker)
    col_ma = ticker.columns.get_loc('MA') + 1
    col_close = ticker.columns.get_loc('Close') + 1
    # Create a chart object of type line
    chart = workbook.add_chart({'type': 'line'})
    # Insert the first dataset into chart
        'name': "MA",
        'categories': [sheet_name, 1, 0, max_row, 0],
        'values': [sheet_name, 1, col_ma, max_row, col_ma],
    # Insert the second dataset in the same chart
        'name': "Close",
        'values': [sheet_name, 1, col_close, max_row, col_close],
    # Configure axis
        'name': 'Date',
        'date_axis': True,
        'name': '$',
        'major_gridlines': {'visible': False}
    # Insert the chart into the worksheet.
    worksheet.insert_chart('I2', chart)
    # Close the Pandas Excel writer and output the Excel file.

    The above code will create an Excel sheet looking like this.


    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