Genius Sheets is an Excel and Google Sheets add-in for retrieving Quickbooks data into Excel and Google Sheets. They provide add-ins for both platforms to allow you to use custom functions to retrieve specific values from Quickbooks and pull reports (profit and loss, balance sheet, statement of cash flows, and other standard reports) into new sheets in your Excel and Google Sheet workbooks.
It's a useful way to replace the usual ways to pull in Quickbooks data - login to Quickbooks, create a report, download a CSV or XLSX file, and then open in Excel or Google Sheets - and through the custom functions, allows you to directly reference accounting data in your functions. It's a great tool to use to automate pulling in actual financials into the Standard Financial Model to create and seamlessly update rolling forecasts and budget variance analyses.
How to use
Here's how to get started with Genius Sheets:
- Create an account at Genius Sheets, and authorize Genius Sheets to access specific companies in Quickbooks
- Install add-in for Excel, by using the Insert > Get Add Ins menu option inside Excel (good instructions from Excel University)
- Install add-in for Google Sheets from Google Workspace
The video above provides a basic walkthrough on how to use Genius Sheets after it's installed; Excel University provides a good walkthrough on how to retrieve data from Quickbooks by pulling reports and using the custom functions. I want to highlight the custom functions, as it's an interesting way to create automatic, always updated financial data into spreadsheets. There are three core functions:
- GS.IS(category, startDate, endDate)
- GS.BS(category, startDate, endDate)
- GS.CF(category, startDate, endDate)
Each function lets you retrieve data from specific categories (replacing 'category' with the account name from your chart of accounts, e.g. revenue, net income, cash) from the IS (income statement), BS (Balance Sheet), or CF (Statement of Cash Flows) by a single date (if only the 'startDate') or summing over a range of data if 'startDate' and 'endDate' are specified. The argument values can be typed in directly, using quotes, or reference cells with the accounts and dates in them, for example:
'=GS.IS("Revenue", "July-2021",)'
'=GS.IS(B3,d2,)
Both can be used as functions to pull data directly from Quickbooks and use in any formula as desired.
One way to specifically use this for Foresight models is for pulling in actual financials into the Standard Model to create rolling forecasts and budget variances. The video at the top demonstrates how this can work; explainers for this are live in the Standard Financial Model for you to build the formulas to best pull in the data for your specific chart of accounts setup.
In the video, I showed a method to link the reports created by Genius Sheets into the 'Hooks' sheet. That works, although a better method for most users would be to use the custom functions to pull the data directly into 'Hooks'. Linking the accounting data into 'Hooks' often takes from grouping of subaccounts - since the model's categories will not map exactly to the chart of accounts - and thus we often have to add together accounts in a few ways to map them to the representative expenses as created and mapped to the categories from the forecast. Genius Sheets makes this easier to do, using the custom functions to add different accounts (e.g. GS.IS("travel","July-2021",) + GS.IS("meals","July-2021") , etc.)
Questions, contact me →
More web tools for building financial models at Alternative to Foresight →