Microsoft has gone public with their Money Excel template to import and manage financial transactions from bank accounts. There are some undocumented tricks available and an important security recommendation that Microsoft overlooks.
Money in Excel can import transaction details (US bank accounts only) and show current balances.
And convert that into snapshot views to see spending by categories, trends and notes on what’s changed.
Who can use Money in Excel?
Excel 365 for Windows and Mac desktop apps have full access to Money in Excel as well as Excel Online, the browser based version.
Excel 365 mobile apps (iPhone, iPad and Android) can view and edit the running template but not update transactions or see the Money Pane.
Install and setup
Money in Excel is an Excel template .xltx, download from Microsoft direct.
A side pane takes you through some of the opening steps. Login to your Microsoft 365 account (why? The user is already logged into their account via Excel 365 itself) and establish a link to your bank account (US only for now).
But the setup doesn’t end with the side-pane wizard. There are more steps under the Instructions tab.
Save and Protect
Most important is saving the workbook and not the way Microsoft recommends.
Money in Excel is a template, like all templates, you need to save a workbook for your own data based on the template.
Security and Privacy
Money in Excel contains all your personal banking details including a link to the latest transactions (real only, thankfully). That means extra security is a very good idea,
Password protect the workbook, especially if it’s saved to OneDrive or other cloud storage.
Microsoft doesn’t even mention protecting the workbook, probably because that suggestion would conflict with its narrative that OneDrive is a ‘secure location’.
Then review the Categories. All transactions can be tagged with a Category Type (Income, Expense or Transfer) and a Name. There’s a list of default and fixed categories that you can add to on the Categories tab.
Import your first bunch of transactions. On the transactions tab choose the category for each item.
Now check out the Snapshot tab to see Excel’s analysis. Most likely you’ll want to rearrange and add some categories to suit yourself.
On the Templates side pane there are more Money related templates to explore.
Net Worth – your overall wealth
Recurring Expenses – what you pay out on a regular basis.
Manually importing transaction data
It’s a real shame that Microsoft has gone the easy route (for them) with only Plaid.com transaction importing supported.
Not all US banks use Plaid and some customers might not be willing to allow a third-party company to access their account (privacy and security assurances notwithstanding).
It’s possible to use Money in Excel without the data import option, with a few limitations. Just enter the transactions manually on the Transactions tab.
The Account pane won’t show account balances and the Update button won’t do anything but otherwise all the fancy snapshots and reports are available.
Microsoft … why not to allow customers the option of adding a bank account manually with no data link?
While Money in Excel is supposed to be for US customers only, people elsewhere in the world could use it with human transaction entry.
Outside the USA
Microsoft says that support for other countries might happen, but no specific countries or timeline is given.
As a guide. the Plaid system is only available in USA, Canada, UK and Netherlands with France, Spain and Ireland in beta.
Perhaps some smarties will make Get & Transform / PowerQuery workbooks to convert other bank transaction details (like CSV downloads) into a form that Money in Excel can understand. It’s another good reason for Microsoft to expand the add-in to include manually entered transactions.
Beyond Money in Excel
You’re not limited to the snapshot and other views offered in the template.
The Transactions data can be used for other views or other workbooks.
In another workbook use Get & Transform | From File | From workbook to import Table1 (not the Transactions tab).
That will load the table (with other hidden columns) into your own workbook. From there you can make all the views and charts you like without touching the original Money in Excel template.
Get and Transform can import and update the transaction data at any time.