Python in Excel 2025 Masterclass for Data Science

Explore the essentials of Python in Excel, outlining must-know basics and common pitfalls. Download accompanying notebooks and workbooks from resources for hands-on practice.

Learn to write Python code in Excel cells with the pie function, run with control enter, and perform integer, string, and list operations, including referencing Excel cells.

The lecture compares Python variables with Excel cell references and explains using names in the name manager. It promotes pythonic variable names in Excel and zero-based indexing.

Explore how Python objects and data types work in Excel, including lists, numpy arrays, integers, floats, dictionaries, pandas dataframe and series, datetime and timestamp objects, and booleans.

Learn how non-type objects (NoneType) represent the absence of a value in Python, with Excel-visible examples from list append, dataframe info, and print outputs.

Explore Python output versus Excel value in Excel, converting lists, NumPy arrays, dictionaries, and dataframes to Excel values or dynamic arrays with spill behavior.

Extract data and meta information from Python objects in Excel using the icon or formulas, displaying array previews, string representations, data types, and type names for various objects.

Explore the diagnostics pane in formulas, inspect python print outs and error messages, learn to diagnose key errors and attribute errors, and manage automatic diagnostics.

Master the calculation order and recalculation for Python coding cells in Excel, understanding row-major execution across rows and sheets, and how misordering can cause errors or stale results.

Explore how the initialization board preloads numpy, pandas, matplotlib.pyplot as plt, statsmodels, seaborn, and excel. Use the first worksheet as an import sheet to add libraries; settings are read-only.

Learn how to define user-defined functions and Python classes in Excel, using the def keyword in Python coding cells, and apply a simple addition function in worksheets.

Learn to create Python plots in Excel with Matplotlib and Seaborn, building a pandas DataFrame from stock data, setting a datetime index, and rendering customizable price charts.

Explore explanatory data analysis of a movies dataset in Excel using Python tools like Pandas, Matplotlib, and Seaborn; download, unzip, and compare Excel and Jupyter notebook code.

Learn to load and inspect a movie dataset, perform univariate and multivariate analyses, and identify factors that drive revenues using pandas, numpy, seaborn, and matplotlib.

Compare project solutions in Excel and Jupyter Notebook, learn to run cells, and analyze data with pd.read_csv, missing values, statistics, histograms, box plots, heatmaps, regression plots, and cat plots.

Import movies.csv into an Excel worksheet via the from text or CSV option, inspect budget, revenue, vote average, and runtime, and prepare for a pandas data frame.

Load an Excel table into a Pandas dataframe, inspect meta information, identify missing values, and generate descriptive statistics to detect outliers and data quality in the movie dataset.

Learn univariate data analysis in Python with Excel, using histograms, violin plots, and box plots to explore numerical features like budget, revenue, and runtime, including outliers and distributions.

Analyze categorical or text data with univariate methods using seaborn count plots to reveal genre frequency, language distribution, and franchise status, while examining year trends and data completeness.

Explore multivariate data analysis to uncover factors driving movie revenues using a correlation matrix and heatmap, regression and categorical plots, all implemented in Python with Pandas and Seaborn.

Create pairwise regression plots with Seaborn to analyze revenue against numerical features like budget, using regression lines and confidence intervals to assess relationships.

Analyze revenue with numerical and categorical movie features using seaborn catplots, compare mean revenues across categories, and visualize with dynamic plots while noting multicollinearity and future regression directions.

Explore project two: financial data analysis with Python in Excel, using Pandas to analyze time series stock data and compare 30 large US stocks, with downloadable project files.

Analyze 30 Dow Jones stocks with pandas in Excel, import data, compute simple and log returns, visualize normalized and non-normalized price charts, and build mean-variance framework with a correlation heatmap.

Explore project two's financial data analysis in a Jupyter notebook, including importing stock data, price charts (absolute and normalized), returns, risk and mean return, and a bond heatmap of correlations.

Load the stocks dataset from stocks.csv into Excel, create a pandas dataframe, and set a datetime index from the date column. Use info to check missing values.

Learn to create dynamic price charts by normalizing stock prices to a base value of 100, selecting up to five stocks, and integrating Python with Excel for interactive visualization.

Select simple or log returns and daily, weekly, or monthly frequency to compute financial returns in Python in Excel, with dropdown menus and awareness of calculation order pitfalls.

Analyze risk–return profile of 30 Dow Jones stocks (2020–2023) by calculating mean returns, annualized returns, and annualized risk, then visualize with scatter plot showing higher returns often accompany higher risk.

Create a correlation matrix from stock returns and visualize it with a seaborn heatmap to reveal pairwise correlations and the covariance matrix's role in diversification.

Explore merging and aggregating data with Pandas in Excel using groupby and merge, and access accompanying workbooks, a solution file, and a Jupyter notebook for practical practice.

Aggregate Olympic medal data in Excel with Pandas by loading Beijing 2008 and London 2012 datasets, then merge via outer, inner, left, or right joins using a dropdown tool.

Learn to load data from Excel workbooks and perform aggregation with group by and stack in pandas, then explore merges and joins with sorting and type conversion.

Load 2008 and 2012 datasets into pandas dataframes, group by athlete and medal, unstack to gold-silver-bronze, and sort to reveal athletes in Python in Excel 2024 Masterclass for Data Science.

Merge 2008 and 2012 medal data with six pandas join types: outer, inner, left, right, left-only, and right-only, on the athlete column, using Python in Excel.

Explore a comprehensive workflow for a movie revenue project using multiple regression and hypothesis testing, with data pre-processing in scikit-learn, feature selection, and Excel integration.

Load the movie dataset, drop the title, and prepare regression-ready data by imputing missing values, scaling numerical features with a robust scaler, and one-hot encoding categorical variables.

Convert a NumPy array into a data frame by extracting one-hot encoded feature names from a pipeline, cleaning headers, and combining with numerical features for regression analysis and revenue insights.

Explore multiple regression analysis and hypothesis testing using ordinary least squares, addressing multicollinearity and dummy variables, interpreting p-values and regression coefficients with statsmodels and Excel.

Copy code from Jupyter into Excel and configure scikit-learn and statsmodels preprocessing pipelines. Run an ordinary least squares regression with selectable features, and address multicollinearity to enable feature elimination.

Learn how to perform feature elimination in a regression model in Excel, reduce multicollinearity, monitor p-values, condition number, and R-squared to identify a set of significant features that explain revenue.

Explore how python in excel and xlwings combine in a real-world project to pull live data from Yahoo Finance and run code in underlying scripts.

Xlwings is a free, open-source Python library for Excel. If you have Anaconda, it comes pre-installed; otherwise install with pip install xlwings and check conda list.

Learn how xlwings acts as a data viewer by exporting numpy arrays and pandas dataframes to Excel, enabling full dataset inspection through created workbooks.

Explore the data viewer update in xlwings by using the table parameter, which defaults to true and formats the Titanic dataset as an Excel table, with sorting by passenger class.

Connect to an Excel workbook using xlwings by creating a new book, opening an unsaved one, or loading a saved file to read and write values.

Learn to read and write values in Excel using xlwings, handling strings, numbers, and datetimes, while selecting sheets and cells like A1 and B1 and clearing values.

Learn how to run external Python scripts directly in Excel using the Run Python feature, and follow setup steps with downloadable files to ensure smooth execution.

Install the xlwings add-in via Anaconda prompt or terminal, restart Excel, enable the developer ribbon, and verify the conda path and environment for one-time setup.

Run your first Python script in Excel using run main to execute a Python function defined in a module and write to a cell.

Troubleshooting covers using official documentation, checking the xlwings add-in setup, and resolving runtime errors like path or environment issues, including python interpreter settings on Windows and Mac.

Explore the bonus project that combines python in excel and xlwings, using a downloadable case study with an excel workbook, python script, and Jupyter Notebook to automate financial data analysis.

watch the final output of a stock data case study as a python xlwings script fetches, updates, and visualizes five stocks with adjustable tickers and time periods from Yahoo Finance.

Connect to the workbook with xlwings and download close prices for tickers using pandas. Write the dataframe to the data sheet and update the status.

Install the Anaconda distribution to get Python and a full data science environment with pre-installed packages. It supports Jupyter notebooks and other IDEs, and helps avoid package conflicts.

Explore the Anaconda Navigator, launch Jupyter Notebook, and run Python code with shift-enter or alt-enter, while managing the base environment and essential packages like numpy and pandas.

Master Jupyter notebooks for data science by learning interactive coding, switching between edit and command modes, using markdown, running cells with keyboard shortcuts, and managing kernels and outputs.