Excel Report Automation with Python

Learn how to automate Excel reports using Python and pandas by loading treasury curve data, cleaning nulls, dropping unused columns, and building comparison tables with renaming and calculating rate changes.

Master handling dates in Python for Excel reports by replacing date components, creating a daily date range, reindexing the yield curve, and forward filling missing values.

Build a function to generate a yield table from a data frame for Excel, aligning current, one-year-ago, and five-year-ago data, with their changes.

Learn to visualize treasury yield curves by converting time indices to year fractions, plotting current and past curves, and saving figures for Excel report automation.

Create a table writing function for Excel that writes dataframe values, optionally includes index and columns, and colors by positive or negative yields in a treasury yield curve sheet.

Install openpyxl and create an in-memory workbook, write values to cells, save as example.xls, and prepare a function to convert yield curve data into a table.

Master Excel worksheet modifications, including converting column numbers to letters, adjusting column widths, and applying bold fonts via copies; also learn to merge cells and generate coordinates for title creation.

Merge cells to create a centered title, apply font styles, and set header text. Add borders, hide grid lines, and insert the yield curves image for the economic report.

Process CPI and real estate index data in Python to support Excel report automation: read, rename columns, parse dates, plot trends with Matplotlib, and prepare data transformations.

learn how to build and use pandas multi indexes from tuples, including year and quarter, and apply them to real estate and CPI data for flexible indexing, grouping, and joining.

Plot real estate data in Python, improving visuals with axis labels, tick rotation, and custom tick labels mapped from indices. Save figures with bbox adjustments to fit text neatly.

Adjust the real estate index for inflation using the consumer price index, compare it to the inflation-adjusted index, and compute quarterly returns with a pivot, then save to csv.

Write the first real estate report by reading data, renaming columns, and writing a table to an Excel workbook, then format axis labels, merge cells, and apply number formatting.

Add color mapping to the Excel report by defining RGB to hex, applying red-green pattern fills, scaling values from -1 to 1, and embedding an image for a data-driven presentation.

Define and assemble final Python functions to automate the Excel report, creating yield curve and real estate data joined with CPI via a year-quarter multi-index.

Develop a data creation workflow that generates year-and-quarter reports by end-of-quarter date. Use modulo to advance quarters and build yield curve and inflation-adjusted real estate data frames.

Define a create report function that takes year and quarter, processes data, generates images for treasury yield curves and real estate vs inflation, saves plots, and writes the Excel report.

Finalize the report by creating the workbook, writing the treasury sheet for the quarter and year, and adding a yield curve and real estate sheets with nominal and inflation-adjusted data.