Automated Baremetrics Spreadsheet

June 5, 2017

Back in April I read a great post by Jaakko Piipponen on the SaaS financial model he'd built as an Excel spreadsheet. I'm interested in spreadsheet financial models, so I enjoyed Jaakko's run down on how his model covers PnL report, operating expenses, payroll and revenue. Jaakko describes how you only need to spend 30 mins a month downloading reports from the Baremetrics dashboard and pasting it into Excel to keep it up to date. The post is titled an "SaaS financial model you’ll actually update", because the value of the model is such that 30 mins of manual handle turning a month is more than justified. Now I'm the first to advocate Excel for business agility; it's a great tool that enables sales, marketing and finance people to solve problems for themselves without the bottleneck of a software development team. But the downside of Excel is manual operation like the download and paste Jaakko spells out. Wouldn't it be great if the Baremetrics data in the spreadsheet downloaded and updated automatically? Then the model would always be up to date, and no tedious and error prone download'n'paste operations are necessary.

So I built it by adding Baremetrics functions to SpreadServe's SSAddin. Here's a spreadsheet that strips Jaako's model down to just the revenue parts of the model. It uses SSAddin functions to automatically download the revenue numbers every 10 minutes into the MRR Export sheet via the Show Summary API so the Revenue Model sheet will update every 10 minutes with the latest new customers, subscriptions, cancellations, upgrades and downgrades. All you need to do is download SSAddin 32 or 64 bit .xll binaries from here install the addin in Excel and fire up the spreadsheet. Don't forget to ensure that auto calc is on, and that you've got a copy of SSAddin.xll.config from the download page and put it next to SSAddin.xll on your PC. You'll need to edit the .config to add your Baremetrics license key. You can see flight_bare_model2.xls running online and driven by data here and here.

The online spreadsheet takes automation to the ultimate level. When you run a spreadsheet model automated with SSAddin on your desktop or laptop you still have to start Excel, load the spreadsheet and hit F9 before auto updates can start. With SpreadServe, you can move the spreadsheet onto a cloud server. All manual operations are eliminated, and everyone can see the same auto updating numbers in their browsers.

SSAddin: Google Analytics, Baremetrics, Quandl & Tiingo

June 5, 2017

SpreadServe has it's own open source Excel addin: SSAddin, an XLL Excel addin exposing APIs for Google Analytics, Baremetrics, Quandl, Tiingo as worksheet functions. It's distributed under a permissive Apache 2.0 license, so you can use, repurpose or redistribute however you like with no fees so long as you include license and copyright notices. You can download 32 and 64 bit .xll binaries from here. Don't forget to take a copy of SSAddin.xll.config too and put it next to SSAddin.xll on your PC. You'll need to edit the .config to add your license keys for Google, Baremetrics, Quandl and Tiingo. There's documentation here but I suggest you start by playing with example spreadsheets from GitHub. Don't hesitate to ask questions in the comments here, or on the download page, or to raise an issue on GitHub.

600+ interlinked Excel workbooks

April 26, 2017

I found this sorry story about an Excel operational nightmare on the /r/Excel sub-reddit yesterday: "a few days back I started working at a new company's finance department. I was shocked to see that all financial reports are built with excel references to other excel references to hundreds of manually filled excel documents. The reference tree goes quite deep up and is rather large: average excel sheet feeds from about 20 external excels, and each of those in turn also feeds from 20 external excels. And so it goes about 16 layers deep. The issue is that each month we need to update all reports, which means we need to open all documents and click "refresh" or update connections. However connections don't update unless all linked excel documents are also opened and updated, and so it goes all the way down to manual input excels. On top of this my computer crashes if I open any more than 4 excel sheets at the same times, because the excel sheets contain dozens of tabs and each tab is a huge table. I am looking for an idea to improve this mess in order to (1) reduce risk for human error (if i forget to update some of the 600+ links, there will be wrong numbers presented to CEO), (2) to reduce the time needed to run this (now takes almost a week to update everything), and (3) do this change fast within 1 month time."

One SpreadServe host deployment and a few days consulting would fix this problem. The one person week to execute the report would be eliminated as would all the errors from manual operation.