Actual PowerPoint Automation with Excel VBA

Examine the loan dataset and adjust its structure, inserting headers and columns for clarity. Confirm unique loan keys, and review key risk metrics such as FICO scores, LTV, and DTI.

Create the Excel workbook with a new results sheet and a time-series of metrics, using array formulas, concatenation, and error handling for credit risk calculations like LTV and DTI.

Master weighted average term calculation in Excel with array formulas, using numerator and denominator, apply iferror, and automate metric updates by replacing column references across many metrics.

Extend the results page with FICO, LTV, and DTI bands, adding units, dollars, and percent metrics, plus checks and formatting for portfolio totals.

Learn to extend the Excel results worksheet by adding 51 state originations metrics with units, percent, dollars, and dollars per cent columns, using hard-coded state codes and error handling.

Continue building the results sheet by expanding metrics to about 300. Use TBD placeholders to organize upcoming calculations and review the design approach that enables quick expansion.

Build a dynamic Excel results page by copying grade-specific dimensions, updating formulas with find and replace, and validating calculations. Prepare for adding purpose, dimension, and channel dimensions before visualizations.

Learn to add a new metric on the results page, replace column references, and compute a weighted average Fico by excluding zero values across dimensions.

Continue building the results sheet by adding purpose and channel dimensions, creating a unique list, and updating references to auto, credit card, debt consolidation, home improvement, other, web, and storefront.

Explore dynamic references in Excel, copy formulas to update sheets, and optimize performance by using manual calculation to counter Excel’s automatic recalculation weaknesses, plus a few time-saving tricks.

Explore using the offset function to create dynamic Excel references that adjust as data grows, by counting nonempty cells and computing height and width, then preview named ranges with VBA.

Apply Excel VBA to automate named ranges with offset, by building and testing period and dim references, deleting and redefining thousands of names, and ensuring contiguity for dynamic charts.

Build and format dynamic charts in Excel using named ranges to visualize units, dollars, weighted average term and rate, and distributions (FICO, LTV, DTI), with automated updates as data grows.

Learn to automate excel charts across dimensions by adding a sheet and using a macro to rename series from total to grade A, then update chart titles.

This lecture shows updating the chart series for the credit card dimension and explains a VBA named range rule: prefix with an X to avoid starting with C or R.

Adjust chart colors for bars and lines with macros using the RGB framework, add labels to all charts, and plan PowerPoint automation from Excel.

Learn to connect workbook to PowerPoint and list charts in the desired order with a VBA macro, organizing 165 charts across 15 groups and listing chart names in column A.

Add a new sheet to the spreadsheet and use column a for slide titles, aligning each title with its slide number, then proceed to charts in the next lecture.

Automate PowerPoint creation from Excel using a VBA macro that pastes charts into slides and saves a multi-page deck.

Create a PowerPoint sections file that maps 15 dimensions into a final presentation, using VBA to combine it with the main deck and align with corporate styling.

Learn to merge section slides into the course_step_one PowerPoint using the insert sections macro, placing headers and charts in the correct order from the sections PowerPoint.

Update and refresh your PowerPoint automation workflow by pasting new data, copying formulas as values, updating charts, and running VBA macros to list charts, create slides, and insert sections.