If you ever find yourself importing historical PayPal transactions into Wave Accounting, you’ll likely discover that PayPal’s exports aren’t very digestible.
The key issues with PayPal CSV files are:
- PayPal’s fees show up in a “Fee” column on the same row as the originating transaction. In Wave, you’ll want these inputted as separate transactions so that they can be properly categorized as “Merchant Gateway Fees”.
- The column names are incorrect, they have to be (Date, Amount, and Description).
- All the transactions, regardless of currency, show up in the same CSV file. To import into Wave you’ll need separate CSV for every PayPal currency account.
There’s some discussion in Wave forums about how to resolve this problem by making new rows, but this takes time.
So, I’ve made a little script in Ruby to help with the task. Here’s a link for the self-sufficient.
Update 2020-05-08: Many people have been having trouble with the page I link below. Most of the problems were because of the various internationalized types of CSV. I think I’ve fixed these problems. Please click the link and try again!
What Does the Script Do?
This script performs the following tasks:
- Creates out CSVs that have the column headers needed for Wave Accounting.
- Breaks the transactions up into separate files, one for every 100 transactions of each currency encountered. This small size is required as per instructions from Wave (see import notes below).
- Splits any transactions that involve a PayPal fee into two, naming the second “PayPal Fee” with its own dedicated Amount.
- Sets the description of each transaction as “Name – Type”, where Name is the recipient, and Type is the type of transaction, e.g., “MostlyDev.com Donation – PayPal Express Checkout”.
If you’re on MacOS you’ve already got Ruby installed — I make a point of not depending on any third-party libraries — so you can run the script as-is.
Running on MacOS
So, to get started:
- Save your PayPal CSV into a folder, let’s say you called it: “PayPal.CSV”
- Download make_wave.rb from Terminal.
$ curl https://mostlydev.com/wp-content/uploads/2019/01/make_wave.rb > make_wave.rb
- Then, run the script on your CSV file:
$ ruby make_wave.rb PayPal.CSV
You’ll see some output like this:
PayPal to Wave-importable CSV converter Copyright (c) 2019 Wojtek Grabski (mostlydev.com) ------ ... created PayPal.usd.wave.csv ... created PayPal.cad.wave.csv ... outputted 2202 rows
You should import each of the resulting csv files into its own currency-specific PayPal account in Wave.
Need to OCR and Import Bank Statements?
If you’re importing paper/pdf bank statements, then check out https://docparser.com. I spent a few days twiddling with OCR before I discovered that these guys have it pretty much figured out. If you need help with importing their CSV exports into Wave, or need some advice on how to best configure, then hit me up
Notes on Importing
Through a lot of trial and error I’ve discovered that Wave is *very* picky about imports, and whatever process performs this task can very easily fail.
To reduce the chances of failure, make sure that after you have commenced the import, and despite what Wave happily tells you, DO NOT LEAVE THE TRANSACTION PAGE, and do not refresh, while the import is progressing. If you stay on the page, and refrain from doing other things, then not only will the import be more likely to succeed, but you’ll also get a visual update when it’s done.
Trust me. Stay on that page, and keep the tab open.
Moreover, even if it looks like Wave has done nothing – there’s no status box on the page after you confirm the import – you should still wait a few minutes and refresh the date range to see if your transactions have imported. I’ve had several occasions where I thought I was re-attempting an import, only to find multiple copies of transactions.
Check your ledger a little after, and between every attempted import. Yes, it’s tedious, but it’s much less tedious than having to sift through and delete 300 triplicate transactions.
Running on Windows
On Windows, check out Chocolatey to most easily get your hands on Ruby. Choco will change your life, I promise.
Install Chocolatey & Ruby on Windows
- Go here and copy the cmd.exe Chocolatey install command under the heading Install with cmd.exe.
- Drop into an Administrative (Run as Administrator) command shell and run the command you copied above.
- Follow instructions.
- Quit your Command Shell and re-open it as administrator.
choco install ruby -y. Follow instructions.
choco install curl -y. Ditto.
- Quit your shell again, re-open (don’t need to be admin this time), and follow the Mac instructions above.
Note: I recommend importing your home currency first. If you don’t do this then Wave will erroneously flag transfers between the non-home currency and your home currency as having come from your native bank account. This creates a bit of work to un-link and re-link to the correct PayPal account.