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 2019-02-11: Someone requested that I make a web page to allow non-tech people to perform this task. Feel free to go there instead of reading all the technical stuff below.
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 http://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
That’s it.
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.
- Run
choco install ruby -y
. Follow instructions. - Run
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.
This is awesome! Can’t believe how bad Wave is at handling different Paypal currencies. I wonder how hard it’d be to turn this into a simple, more user-friendly program?
The only big complaint I have is about how transfers are pre-linked. I’d rather none of them were pre-linked than have to unlink and fix 20-25% of my imported transactions.
Did you mean turning the script into a program? Yeah, I’ve been wondering whether I should’ve just turned this into a web app. It wouldn’t take more than a couple of days. I suppose if one or two people ask then I’ll probably do it.
I made the simplest of PHP (yuk) pages to perform this task on request. It’s here http://mostlydev.com/make-waves/
Just saw your response. Can’t believe you actually made a web app for it. Going to try it now. Thanks!
Great, let me know if it works for you. You might well be the first person to actually try it who isn’t me.
Thank you for working on this huge gap in Wave 🙂 I’m going to attempt to process my .csv file as described above, although I understood about 1/3 of the instructions, lol. I’m 60 years old and pretty tech savvy for my age, but the struggle is real . . .
First let me say if you made this into a web app, I’d totally buy it. I’d send you a $50 donation or what ever you feel is fair within reason.
Secondly, when you download the .csv from paypal, (I do it in the Reports Tab) do you select “Completed Payments”, “All Transactions” or “Balance Affecting” My instinct is completed payments
Hi! Sorry about the delay in response. Apparently I need to check my comments more often.
You want to download “Balance Affecting” transactions. Thanks for your interest in a web app that does this.. I’ll see if i can whip one up in the coming week or so.
Here. I’ve made a *very basic* page that does all this and sends you a zip file containing all the wave-compatible CSVs and then deletes everything immediately. http://mostlydev.com/make-waves/
Can you give me more instructions on how to run on MacOS? I don’t know what “Run the script on your CSV file” means?
> “Run the script on your CSV file”
This means quite literally running the command that appears right below it.
$ ruby make_wave.rb PayPal.CSV
Without the dollar sign — that’s just a convention for expressing terminal commands.
To run the command you have to open the Terminal application, navigate to the folder where you downloaded the script and your csv, usually by typing:
$ cd ~/Downloads
And then running the aforementioned command.
Hi KR. I’ve actually made a website for converting PayPal CSV to Wave. Here’s the link:
http://mostlydev.com/make-waves/
You don’t have to run any scripts.
Thanks so much for this script!
I tried running the Wave Maker after uploading my Paypal CSV file, but the zip file it creates in empty.
Am I missing something?
Please try again
Thank you! I almost decided to give up on Wave and move to something paid, but they I found this.
No worries. Sometimes dev teams need help with the back burner stuff.
Hello,
I sent an email but the website you listed isn’t working. Is it offline?
I really need this service so I hope I get a reply soon 🙂
Hey, thank you so much for making this! I’ve been really frustrated with Wave since they removed the PayPal integration.
I seem to be having some issues with the web tool, though. I upload the CSV to the web tool successfully and then it sends me back a zip file. However, the zip file seems to be empty. Any idea what’s going wrong?
Thanks!
Working on a fix for this. I moved servers recently and apparently there are some issues.
I tried using you web page and I keep getting an empty file.
Yes, someone else has reported this too. I’m working on a fix.
I’ve also tried doing it with the power shell and get this in return:
:85:in `require’: cannot load such file — os (LoadError)
from :85:in `require’
from make_wave.rb:40:in `’
Just need to add the `os` gem by running:
$ gem install os