Downloading iTunes finance reports into FreeAgent
Just over a year ago, I posted a comparison between two popular online accounting tools, Xero and FreeAgent. As I concluded, I found that FreeAgent worked best for small businesses and freelancers like myself and I've been using it ever since.
A portion of my company income comes from the sales of my iPhone app, Squeemote (and I have other apps in the pipeline). Each month, I receive a finance report from Apple that I can download from iTunes Connect detailing exactly what my sales are for that period. If the sales in a particular region have reached more than $150 (or the equivalent), the outstanding balance will be remitted to my business bank account and I will receive a remittance receipt from Apple.
Tracking app sales, the manual way
Ever since I started selling on the App Store, I've been using a simple Google spreadsheet to track my sales in each region and I still do. I don't use this for accounting purposes, but to track my sales in each region, how much I'm due from Apple in each region and the amount received in GBP. This helps me keep an eye on when I expect to receive money for a particular region and how much money I've made from Squeemote.
Besides this, I also need a way of getting the figures into FreeAgent to keep my accounts up to date. Previously, I did this manually and the steps I'd follow each month were roughly as follows:
- Log in to iTunes Connect and download the finance reports for the previous month.
- Enter the figures into my Google Spreadsheet.
- Wait for the remittance advice from Apple to confirm that I have received payment for a particular region.
- For each region I've received payment for that month, manually create an invoice in FreeAgent detailing the number of units and the unit price from me to Apple (this will be 70% of my app's sale price net of any sales tax)[^1] in the appropriate currency. If the payment covers more than one period (because the previous month amounts were under the $150 threshold), I use one invoice line per month.
- Confirm the total matches the finance report.
- When my bank statement has been imported into FreeAgent, explain the transaction from Apple as full remittance of the appropriate invoice; FreeAgent accounts for and reports any exchange rate losses or gains.
It only took several months of doing this before I started to get fed up and realised I must be able to automate some, if not all of the process. Fortunately, FreeAgent has an API so that solved part of the problem; iTunes Connect has no such API unfortunately.
Automation step one: fetching the reports
I decided to split the problem into two parts, the first being fetching the reports from iTunes Connect. Using the Mechanize library, I was able to write a simple Ruby script that logged into iTunes Connect and pulled down the last 10 finance reports, discarding any that had already had been downloaded, and sorted them into a folder for that particular period.
I run this once a month once I have been notified by Apple that the reports are available. I end up with something like this in my Documents folder:
At this stage I still enter the total sales from each report into my Google Spreadsheet manually, including any payments received. It only takes a minute or two.
This script can be used whether you use FreeAgent or not. You will need to install the 'mechanize' Ruby gem to use it.
Download fetchfinancereports.rb from Github
Step two: getting the data into FreeAgent
Using another Ruby script, I can process one or more finance reports into a single draft invoice. I could have the script mark the invoices as sent but I prefer to manually double-check them and mark them as sent manually. The script used to deal with currency conversion automatically but it no longer does this as FreeAgent now has multi-currency support.
Using the script is fairly straightforward. Lets say I've just downloaded my finance reports for June and I know I've made enough in the EU region and have received a payment for Apple. In this case, I simply run my script against the June EU finance report:
$ cd ~/Documents/FinanceReports
$ process_finance_report 2010-06-June/80062465_0610_EU.txt
This will create a single invoice with a single line for that report, calculated using a unit price and the number of units sold.
Alternatively, let's say I hadn't received a payment for the Worldwide region for the last 2 months but as of the latest report, I've made enough sales and receive a single payment for May and June. Again, I would run my script but against both reports:
$ process_finance_report \
2010-06-June/80062465_0610_WW.txt \
2010-05-May/80062465_0510_WW.txt
This will again create a single invoice but with a line for each month.
The final part of the equation is making sure the invoices are addressed to the right company. For each region, the payment is received from a different Apple entity; in the US it is Apple Inc, in the EU it is Apple SARL, in Canada it is Apple Canada and so on. My script handles this too although it requires a bit of information up front; after creating different contacts in FreeAgent for each entity, you need to update the script with the correct FreeAgent contact ID. This only needs to be done once.
Download processfinancereport.rb from Github
The required Ruby gems are listed at the top of the script.
If the script seems quite long, its because I've taken the various components (such as a simple Ruby wrapper around the FreeAgent API) that I have stored separately and combined them into a single script. There are some variables at the top of the script that need to be customised before you can use it.
You're welcome to use and modify this script however you see fit. If you modify it, it would be cool if you forked the Gist and made your changes there so anybody can see them but you certainly don't have to. If you have any issues with the script, you can send me a message on Twitter.
Update, 15 Nov 2010
Since Apple started paying out for all regions in a single payment each month, I've been able to automate this process further, which you can read about in this blog post.
[^1]: The treatment of Apple App sales for the purposes of VAT is quite complicated but in short, for VAT purposes there is considered to be two transactions; one from me to Apple and the price to the end customer which includes Apple's 30% share of the sale subsumed in the net price, plus any local sales taxes. The transaction between me and Apple for any region is outside the scope of UK VAT. For more on this, see my explanation on the FreeAgent customer support site.
