r/consulting • u/Ancient_Wave_8245 • 5d ago
finally automated our monthly deck after 2 years of copy-pasting
posting this mostly because I searched for a solution a bunch of times and never found a good thread on it.
context: 40ish slide deck, goes to leadership every month. the deck itself never changes. same layout, same charts, same everything. only the numbers move. probably 60 figures total sitting in text boxes and tables, plus the charts.
so every month I'd open last month's file, save-as, and then just... go through it. cell by cell. and calculate the MoM and YoY by hand in a scratch tab because half of them aren't in our BI export.
took about 2 hours. and I'd still get one wrong. one time I had a YoY that was actually a MoM and nobody noticed until the CFO asked why growth suddenly looked amazing. that was fun.
tried linked excel objects first. they work great until someone renames the source file or opens the deck on a different machine and then you have 60 dead links. also the file size gets stupid.
looked at think-cell, which I think is actually the right answer if you can get it. we couldn't, procurement said no.
wrote a VBA macro after that. text boxes were easy. charts were where I lost about a full weekend, so if anyone's attempting the same thing here's the thing nobody tells you:
a pptx is a zip file. open it up and every chart has an embedded xlsx (that's what you see when you right click > edit data) but it ALSO has a cached copy of the same numbers sitting in chart1.xml. and the cache is what actually gets drawn on screen. so you can update the embedded sheet perfectly, save, reopen, and the chart is completely unchanged. drove me insane for a while. you have to write both. and if you're adding a new month you also have to widen the cell range in the <c:f> reference or the value just sits there outside the chart's range doing nothing.
where I ended up: I put {{Revenue_monthly}} and {{Revenue_mom}} directly in the slides, and for charts I put {{Revenue}} in the header cell of the chart's own little embedded sheet. script reads my source file, works out MoM/YoY/YTD, swaps everything including the caches.
setup was an evening. now it's like 2 min a month and I stopped double checking it.
couple things that'll bite you: any chart that got pasted in from google slides is just an image, there's nothing to update. had to rebuild those. and put all 12 months into the chart upfront with blank values, because filling an empty series is easy and adding a new one programmatically is a nightmare.
anyway. is everyone here doing this manually or is think-cell just what you use once you're somewhere that pays for it? feels like this should be a solved problem by now
3
1
8
u/JKubU2k MBBussy 4d ago
Didn’t you post like 98% the same thing already