How to Run Python in an Open Excel Workbook (EASY) | Combine Excel & Python | xlwings Tutorial
Utilize the power of python to upgrade your Excel functionalities. In this xlwings tutorial, I am going to show you, how to run Python(scripts) in an open Excel Workbook.
xlwings can leverage Pythonโs scientific packages (NumPy, Pandas, SciPy, sci-kit-learn, etc.) to write tools with Python instead of VBA. At the end of this tutorial, I show you also some practical examples. Those examples will include plotting & updating stock market information, as well as interacting with APIs & return the result to Excel and even creating a PDF stock report.
However, in this tutorial, I will not cover how to execute a Python script from excel. I will make a separate video on this.
📝 Resources:
All files of the tutorial can be downloaded here:
Download Here
xlwings Documentation: https://docs.xlwings.org/en/stable/
👩💻 Source Code:
# # ๐ How to Run Python in an Open Workbook # ๐ Link to Documentation: https://docs.xlwings.org/en/stable/index.html # ### Datasets [for demonstration purposes] # #### 1. Stock Market Data ๐ # Import libaries import pandas as pd from pandas_datareader import data # Get Stockdata (only adjusted close) def retrieve_stock_data(ticker_symbol): df = data.DataReader( ticker_symbol, start="2020-12-1", end="2020-12-31", data_source="yahoo" )[["Adj Close"]] df = df.rename(columns={"Adj Close": f"Adj Close {ticker_symbol}"}) return df df = retrieve_stock_data("TSLA") # Print out head df.head() # #### 2. MPG Dataset ๐ mpg = pd.read_csv( "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/mpg.csv" ) mpg # # 0. Installation & Import of xlwings # Install xlwings into the instance of Python that launched the notebook # xlwings comes preinstalled with Anaconda. Make sure to update your version to use the latest features get_ipython().system("pip install xlwings --upgrade --quiet ") # Import xlwings import xlwings as xw # # 1. Quick & Easy Way # View Dataset in Excel xw.view(mpg) # View Dataset in Excel xw.view(df) # Load selected data into pandas dataframe (New in version 0.22.0.) ticker_df = xw.load() # Check type of ticker_df type(ticker_df) # # 2. Connect to Excel Workbook # ## Three different scenarios [New Book, Unsaved Book, Saved Book] # ๐ **`xlwings`** Documentation: https://docs.xlwings.org/en/stable/connect_to_workbook.html # ## 2.1 Connect to new workbook # Open up a new Excel Workbook wb = xw.Book() # Define sheet sht = wb.sheets[0] # Write Data to cell A1 sht.range("A1").value = ticker_df # ## 2.2 Connect to an open, but unsaved workbook # Connect to unsaved workbook wb = xw.Book("Book2") # Define sheet sht = wb.sheets[0] # Write stock data to worksheet sht.range("A1").value = df # Save Workbook wb.save("stocks.xlsx") # Close Workbook wb.close() # ## 2.3 Connect to an open and saved workbook # Using pathlib (works on Mac & Windows) from pathlib import Path # Standardlibrary (no need to install) filename = Path("stocks.xlsx") # Connect & open a saved Workbook wb = xw.Book(filename) # Specify Worksheet sht = wb.sheets["Sheet1"] # # 3. Practical Examples # ## 3.1 Basic Examples # Add a new sheet sht = wb.sheets.add("Basic Examples") # Read single cell from Excel ticker_symbol = sht.range("A1").value # Read range from Excel ticker_symbol = sht.range("A1:A3").value # Insert Formula sht.range("B1").formula = "=A1" # Read Name Range months = sht.range("months").value # Create Name Range sht.range("A1:A3").name = "ticker_list" # Read data from excel, store values in pandas dataframe new_df = sht.range("D2").options(pd.DataFrame, expand="table").value # Save workbook as PDF wb.to_pdf("MyPDF") # ## 3.2 Plot Stockprice in Excel Workbook # Import Matplotlib import matplotlib.pyplot as plt # Get Adj Close Price for given Tickersymbol and store values in a dataframe df = retrieve_stock_data("GOOG") # Plot Adj. Close Price fig = df.plot(figsize=(12, 8)) # Get Figure Object fig = fig.get_figure() # Add a new sheet sht = wb.sheets.add("Plot Stockprice") # Add Picture to Excel sht.pictures.add(fig, name="Stockprice", update=True) # ## 3.3 Connect Workbook to API's # Install package into the instance of Python that launched the notebook get_ipython().system("pip install requests --quiet") # Imports import requests import urllib.request # ### 3.3.1 Useless Fact # Get Random Fact via API def get_useless_fact(): URL = "https://uselessfacts.jsph.pl/random.json?language=en" useless_fact = requests.get(URL).json()["text"] return useless_fact # Add New Worksheet sht = wb.sheets.add("API") # Save Random Fact in Worksheet sht.range("A1").value = get_useless_fact() # ### 3.3.1 Insert Image to Workbook (DOG API) # Image location/name filename = Path("dog.jpg") # Retrieve & Save Dog Image def get_dog_image(filename): API = "https://dog.ceo/api/breeds/image/random" # API Link URL = requests.get(API).json()["message"] # Get URL of dog image dog_image = urllib.request.urlretrieve(URL, filename) # Download dog image return None # Get a new dog iamge get_dog_image(filename) # Insert image in Excel sht.pictures.add( filename.absolute(), # Path to Image name="Dog", # Name requiered to update the image update=True, ) # Updates the image # ## 3.4 Generate Automated Stock Reports # Import quanstats library import quantstats as qs # Specify excel file location (works on Mac & Windows) filename = Path("stock_report/stock_report.xlsx") # Open Workbook wb = xw.Book(filename) # Select Sheet sht = wb.sheets["Report"] def generate_stock_report_as_pdf(): # --- EXCEL DATA --- # Get the ticker symbol from Worksheet ticker = sht.range("B3").value #################################################################### # --- DO SOMETHING WITH THE EXCEL DATA IN PYTHON --- # Fetch the daily returns for a stock stock = qs.utils.download_returns(ticker) # Image location snapshot_path = Path("snapshot.png") heatmap_path = Path("return_heatmap.png") # Title of Snapshot Report title = f"{ticker} Performance" # Generate snapshot report of any given ticker symbol stock_snapshot = qs.plots.snapshot( stock, title=title, savefig=snapshot_path, show=False ) # Generate montly return heatmap stock_heatmap = qs.plots.monthly_heatmap(stock, savefig=heatmap_path, show=False) ###################################################################### # --- RETURN RESULT BACK TO EXCEL --- # Add Picutures to Excel sht.pictures.add(snapshot_path.absolute(), name="snapshot", update=True) sht.pictures.add(heatmap_path.absolute(), name="heatmap", update=True) # Save PDF Report wb.to_pdf("stockreport") return None # Generate Stock Report and save as PDF generate_stock_report_as_pdf()