For years, I struggled to settle on an accounting workflow I truly liked.
In the past 8 years of MacStories, I’ve tried organizing financial records and statements with plain text files and PDF documents; I’ve used and then abandoned dedicated finance management apps; for a couple of years, I even tested a combination of Dropbox, Excel, and Editorial to visualize transactions and generate invoices with a Markdown template. My Italian bank doesn’t support direct integrations with third-party accounting services, and my particular requirements often include converting expenses from USD to EUR on a per-receipt basis.
Eventually, I always managed to keep my records up to date and neatly sorted with the help of an accountant, but I never loved any of the workflows I had established. In the end, several factors contributed to begrudgingly assembling reports and statements with systems I didn’t find flexible enough.
Last year, I discovered Shoeboxed. It was a dream come true. Powered by a mix of computer vision and human editors, Shoeboxed is a web service that can receive PDF expenses, analyze their contents, categorize them based on type and vendor, and even convert dollars to euros based on the historical exchange rate.
The latter had been, by far, the most time-consuming aspect of any system I tried. When it comes to filing expenses from a past date and checking their original amount against my bank records, the optimal way is to convert currencies using a historical exchange rate. None of the apps and services I had tested before Shoeboxed supported that functionality, which forced me to manually convert hundreds of expenses every year to double-check their amount in EUR. There are websites that can do it, but they don’t have an API, nor do they support input from an existing spreadsheet. Also, they’re not pretty.
Shoeboxed was the proverbial glass of ice water for someone in bookkeeping hell. Using the Shoeboxed web app, email forwarding, or its integration with Scanbot, I could send any expense as PDF to the service, which would come back a few hours later with a new record automatically filed and converted for me based on the date matched inside the PDF. It felt like magic. At the end of last year, I simply had to generate a full report on Shoeboxed, export it to a spreadsheet, and combine it with a Dropbox1 folder full of PDF invoices, expenses, and bank records to send to my accountant.
For all its technical prowess and convenience, though, Shoeboxed wasn’t perfect either. The company’s iOS app isn’t as full-featured as the web version, which has been designed with desktop browsers in mind. Like most web apps that haven’t been optimized for touch, Shoeboxed is barely usable on Safari for iPad. I like the unique idea behind Shoeboxed and its underlying technology, but the service – from the lackluster iOS client to the web app – isn’t designed for iPad-only users.
A couple of weeks ago, Apple released a major update to its iWork suite for Mac and iOS. Normally, I wouldn’t peruse the release notes of an iWork update because Apple’s productivity apps never appealed to my creative or financial needs. But Numbers 3.1 for iOS caught me by surprise.
The app’s layout, already optimized for the 12.9-inch iPad Pro since version 3.0, now sports a contextual menu to speed up cell selection and editing. International users can customize dates, times, and currencies for a specific language or region on a per-spreadsheet basis – useful for those who, like me, use their devices in English but don’t live in the United States. And more importantly, among other additions, Numbers 3.1 features new functions to calculate the exchange rate between two currencies at any given point in time.
As I started experimenting with Numbers, I realized that the changes in version 3.1 could allow me to migrate from Shoeboxed to a system that was not only native to the iPad, but also optimized for the iPad Pro. Switching between bookkeeping apps is never fun, but I figured that, with a couple of days, I could transition to a more solid, integrated, and relatively future-proof setup.
And that’s what I did. Two weeks ago, I sat down with my iPad Pro and moved my entire accounting system to Numbers. This includes revenue breakdowns by month, tax calculations, expense tracking, charts, and an automated PDF archival system for expenses. I have already shared documents generated by Numbers with my accountant, who also approved of the way I’m tracking different aspects of my business.
This is a very personal setup, and I can’t share complete spreadsheets with real data. Thus, I’m going to provide a general overview of the new features in Numbers 3.1 I’m taking advantage of, as well as an explanation of functions, formulas, and additional tips that have enabled me to create an accounting system in Numbers.
Numbers’ Interface Update
The most controversial change in Numbers 3.1 is the adoption of a new ‘Cell’ button aimed at simplifying cell formatting and insertion of formulas, dates, and times. The new button and associated popup menu replace the standard tap & hold behavior (for copy & paste) and dedicated software keyboards for entering numbers and dates.
It’s easy to understand why Numbers’ existing user base isn’t reacting positively to such a major change. The update is a significant departure from Numbers’ previous implementation of cell editing. If you don’t use an external keyboard and you’re not editing text inside a cell, copy & paste operations can now only be accessed through the ‘Cell’ menu, which is slower to activate than the standard clipboard menu as it requires moving your eyes from the cell. Defaulting to the QWERTY keyboard after double-tapping a cell also requires a fundamental rethinking of how formats are applied to cells. As the App Store shows, a lot of users are disappointed and confused by Numbers’ new interface.
In an effort to simplify and speed up editing and formula insertion for everyone, Apple has alienated a portion of the core Numbers user base – people who have been trusting and relying on Numbers for seven years.2 It’s always tricky to ship a piece of software that can scale for novices and power users at the same time, but I believe Apple should find ways to alleviate the problems caused by Numbers 3.1 to more expert users – such as providing more customization options when editing cells and re-enabling the traditional copy & paste menu.
In this case, however, I am the novice, and the new Numbers is easier for me to operate. I don’t have years of muscle memory and shortcuts ingrained in my usage of the app. From my perspective, the Cell menu is a reasonable addition, as it surfaces common controls that have enabled me to set up different types of formatted cells within minutes. From the menu, I can access unified clipboard features in a bar at the top, and there are buttons to insert the current date, a new formula from scratch, or a formula from a list of suggestions. There’s also a large and obvious Autofill button in the upper half of the menu.
With a combination of the Cell menu and the Format panel (which is displayed on the right side of the UI on the 12.9-inch iPad Pro), I set up cells with dates, currencies, popup menus, and rich text that I have duplicated across a spreadsheet for every month of the year. For someone without a pre-existing knowledge of Numbers, setting up a new spreadsheet has been incredibly intuitive and fast with version 3.1 of the app. Unlike Microsoft’s ribbon UI in Excel, Numbers’ controls feel native to the platform; the entire app has a sensible organization of nested menus (such as font options) and solid support for iPad-only features. Numbers can be used in Split View, the Format pane adapts to the iPad Pro’s display, it has keyboard shortcuts, and it offers extra buttons in the Shortcut Bar. There’s an overall fluidity and polish to Numbers’ iPad interface that I appreciate after struggling with Excel’s Windows-like UI and the design atrocities of Google Sheets.3
I should also mention the ability for Numbers 3.1 to override the system’s language and region setting for individual documents. Available in the ‘More’ menu (the button with the three dots in the top toolbar), a new ‘Language & Region’ screen allows you to select different regional settings, which will be automatically applied to dates, times, and currency amounts in a sheet.
This is a terrific addition for my peculiar needs, and one that I wish Apple would adopt in other apps as well. By setting my spreadsheets to Italian/Italy, I no longer have to worry about cells being formatted with American dates or USD before sharing a document with my accountant.4
I used Numbers on specific occasions in the past (an example), but I always found it somewhat difficult to get started assembling a spreadsheet from scratch. From a usability standpoint, I believe the new Numbers accomplishes its goal of helping spreadsheet novices easily format and manipulate data. At the same time, though, I can see why these changes have irked more proficient Numbers users, and I think their concerns are valid. It’ll be interesting to see if and how Apple can find a better balance between the old and new.
Calculating Revenue and Taxes
As soon as I decided to move to Numbers, I recreated my spreadsheet to track monthly revenue and calculate tax payouts.
This was relatively easy to build as it’s mostly based on an existing system I was using in Excel before. There’s a spreadsheet with multiple sheets in it: the first one is an overview of the entire year, followed by sheets for individual months. There’s also a chart in the first sheet to visualize revenue sources by type.
Every month, I download a complete report from my work bank account with a list of every incoming transaction. The bank’s .xlsx file contains additional data and graphics I don’t need, so I only select the portion I want and paste it in the sheet of the month it belongs to. At the top of the sheet, there’s a section of rows that look for specific words in the name of a deposit to identify the sender. Each row represents a different income source – such as Stripe or iTunes – and the sum of each match yields the total for each category.
This can be built using the
SUMIF function – a handy tool to sum multiple values within a range only if they satisfy specified conditions – in my case, some plain text to be contained in a ‘Description’ column. The formula looks like this:
What this formula says is: “If you find a match for “Stripe” between cells F25:F69, sum the corresponding values of cells D25:D69”. By repeating this formula for different types of deposits on my bank account, I can accurately track multiple revenue sources for each month. Then, I can sum all the categories to get a complete monthly revenue overview, and use another formula to calculate how much I’ll owe the government in taxes.
SUMIF function isn’t exclusive to Numbers, but I find it interesting and useful nonetheless. The function works by evaluating a set of test values – in this spreadsheet, the Description column with details on each incoming transaction. Optionally, you can provide sum values to the function, which correspond to cells on the same row; if you don’t pass sum values, however,
SUMIF will simply evaluate whatever it finds in the test ones.
Speaking of evaluation, Numbers uses an expression that outputs a Boolean value of
FALSE. Expressions can contain comparison operators, references, concatenations, and wildcards – which should be familiar to anyone who’s ever dabbled in regular expressions. In my monthly revenue sheets, I use the multiple-character
* wildcard to match any character surrounding the name of the category I’m looking for.
At this point, I’m still pasting the total of each month manually into the first sheet (the one with the annual revenue overview). I should probably consider automating this process with references to specific cells on different sheets, which I’m going to tackle next.
Organizing and Calculating Expenses
Whether I was using Google Sheets, Excel, or reports from Shoeboxed, for the past few years I’ve always tracked monthly expenses with a spreadsheet. With the latest Numbers, I’ve been able to create a “smart” spreadsheet that, besides tracking items and summing them up, also converts expenses from USD to EUR if necessary.
Every month, I deal with expenses in two currencies. One of the best aspects of Shoeboxed was that it could intelligently understand when to convert USD to my primary currency; I wanted to create a similar system in Numbers. With a couple of nested conditions and the new
CURRENCYH function, I’ve come up with an expense sheet that converts American dollars to euros only if an original amount in EUR is not present, using the row’s referenced date for the historic exchange rate.
First, let’s consider how I organized the sheet. There are 9 columns:
- Type (single payment or subscription)
- Kind (website-related cost or other expense)
- USD amount
- EUR amount
- Link to PDF file
While the Description column contains text fields where I type expense descriptions manually, the other detail fields (Category, Type, and Kind) are cells that use a popup menu. I find the popup format to be an effective solution for items that carry one among multiple fixed attributes, and they’re easy to put together. To make the spreadsheet more fun and legible at a glance, I spiced up the Category popup menu with some emoji. My accountant likes them, too:
The detail fields and URLs in the Link column have no effect on the total calculation in Column I. The date and amounts in the currency columns are the essential pieces of the formula – especially because columns F and G use the currency code as header (more on this below).
Now, let’s look at my first two expenses of the year to understand my problem:
While the first expense took place in Italy and uses EUR as currency, the second one is in USD. Thus, I need a system that can skip currency conversion if the original expense is already in EUR, but calculate the conversion rate if the original amount is in USD. In addition, the conversion needs to use the value from the Date cell to provide a historically accurate conversion.
There’s an easy way to check if a cell is empty in Numbers: the
IF function combined with an
ISBLANK condition. The
IF function, as in most programming languages, is based on an expression that is evaluated by the program; this expression can output different data if the expression returns
False.5 On the other hand,
ISBLANK returns a Boolean value depending on whether a selected cell is empty or not.
In my case, I set up the sheet so that each cell in the Total column contains this formula. If the USD cell on the same row is blank (
True), it means there’s a EUR amount for the expense, so the Total cell can use it. If the check returns
False, however, a currency conversion from USD needs to happen.
There are two types of currency functions in Numbers 3.1:
CURRENCYH. Both functions return data about the exchange rate between two currencies, and they need an Internet connection to retrieve exchange rates remotely.6 The difference between the two is that while
CURRENCY calculates the exchange rate for the current day,
CURRENCYH returns historical data on the exchange rate for any given past date. Both functions need a currency code as input, but
CURRENCYH also requires a date string to perform the historical lookup.
With these elements in mind, here’s an example of what the complete formula I put in every cell of the Total column looks like:
IF(ISBLANK(F71),G71,F71 × CURRENCYH(F$1,G$1,0,A71))
Or, in English:
- If cell F71 is blank, use value from the EUR column in cell G71. Otherwise;
- Perform a historical exchange rate lookup for the date in cell A71 using currency codes in cells F1 and G1, calculated at the close of trading day;
- Multiply USD amount from cell F71 to the just-calculated historical exchange rate to obtain a date-adjusted amount in EUR.
This process isn’t as automatic as throwing a PDF into Shoeboxed: I need to enter and categorize every expense manually. However, that’s a trade-off I accept if it means fewer services to pay for and a single spreadsheet that looks great on the iPad Pro.
Historical exchange rate calculation is such a niche feature, I’m surprised Apple shipped it. And in my tests,
CURRENCYH has performed remarkably well, correctly calculating the exchange rate for hundreds of expenses in my document.
There are some aspects of this feature I’d like to see Apple improve, though. Some dates – such as past holidays and no-trading days – display an error if an exchange rate isn’t available for that day. I’d like to see Numbers offer an option to calculate the historical exchange rate based on the nearest available date or from a range of dates.7 And, there should be a way to perform a full currency conversion from an original amount to another currency instead of having to multiply by a baseline like I do.
Overall, I’m happy with the presentation of my new expense-tracking spreadsheet and its currency conversion functions. To make it easier for my accountant to parse individual months, I’ve added colored separators between months. You might notice that I’m using San Francisco Text and Display as fonts; I downloaded both typefaces from Apple’s developer website and installed them with AnyFont so I decide where I want to use which. Under each month, I’ve also included a breakdown of monthly expenses by category.
Finally, there’s an automation angle to my move to Numbers, too. With Numbers and Safari/Newton in Split View, I can quickly upload PDF receipts to a predefined Dropbox folder. All it takes is running my ‘Save Expense’ workflow from the action extension on any PDF document.
To save time when filing PDFs into the appropriate sub-folders in my Dropbox, the workflow looks at the PDF’s file name and, if it finds a match for recurring expenses (such as Stripe, Rackspace, or Writers), it uploads the file to the category’s dedicated folder. The workflow then copies a link to the newly uploaded file to the clipboard, which I can paste into Numbers’ Link cell. I can go from a PDF on my iPad to a Dropbox link for an intelligently-filed PDF in under 15 seconds, which is pretty neat.
Numbers’ new currency functions are fantastic improvements for small business owners who operate in the Internet era at a global scale in multiple currencies. I have a lot of ideas on where to take my accounting system next: I’d like to handle even more currencies at the same time (for the occasional GBP expense, for instance), and I want to figure out a way to compare monthly revenues to expenses automatically, rather than manually as I currently do. I want to create more charts, calculate monthly averages for recurring expenses, and I need to set up iCloud launchers to quickly access my spreadsheets from anywhere.
I have a lot of work ahead to further optimize my finance management system for Numbers. But I feel like I’ve found a new home and, after several years, a system that I can fully customize to my needs.
- In 8 years, Dropbox has been the only constant in my accounting system. All my most important documents are stored and shared with Dropbox. ↩︎
- Probably even more – I’m only considering Numbers’ original iPad release in 2010. ↩︎
- Though, to be fair, Google Sheets has the huge benefit of integrating with automation services like Zapier. I can’t use automation for accounting, but I recommend Google Sheets if you want to automate data collection in an online spreadsheet. ↩︎
- Interestingly, once you enable collaboration on a spreadsheet, you can no longer change its Language & Region settings. Apple says that “adjusting spreadsheet language and region settings while collaborating is coming soon”. ↩︎
An expression can also output a number;
0is treated as
False, while any other number is considered to be
- According to Apple’s documentation, financial data is provided by Yahoo. ↩︎
Which I believe Google Sheets supports with the