Progress feels slower right now, but that's because I'm having to learn VBA for Excel. Once I get this step done, this project is going to move much, much faster.
Click here to read the first part of the series if you're new here.
Last time, I talked about currency conversion and how it's a unique problem on royalty reports.
Amazon and Google Play do not list the USD currency you get paid in for foreign transactions. Amazon lists their exchange rates and your actual payment amount in USD on a separate report. That means whatever I import from the Amazon report won't be accurate. (I made 20 GBP this month! Awesome!!! Wait…what is that in dollars?”)
Google just converts the foreign transactions for you and pays you in USD, but you can view the exchange rates on their dashboard.
In order to get the correct royalty amounts into the database, I have to convert the Amazon marketplace currencies, which presents a unique issue that can be solved by Excel VBA. (Far better to get the data right BEFORE it goes into Access).
Making the KDP Old Report Look New Again
I've talked numerous times about how Amazon had an “old report” they used from 2012-2016 that was a dumpster fire.
Well, VBA can help us clean that report up. I spent all night learning VBA code (actually, I just copied and pasted other people's code from forums, to be honest), and I was able to create a macro in Excel that takes the report from zero to hero in about 2 seconds.
See it in action:
That, my friends, is how you roll up the KDP Old Report and smoke it. How cool is that?!
That's just a test. I have some tweaks to make still. I'll share the final macro in a little while in case you want to do this for your own reports.
In a nutshell, here's what I did:
- Copied the existing report to a new tab to preserve the original report in case I need it for some reason
- Created a tab for currency conversions, with a row for each Amazon marketplace. I had the macro copy in the historical average since 2014 for each Amazon marketplace (which I hard to find and hard code in myself). That's my starting point.
- Prepare the document for Access so that I can import it with little effort
I just have to do a little tweaking to get a few fields to populate. I had to call for some help and I have someone writing a couple lines of VBA that I couldn't manage myself. That will be ready in the next day or two.
Now comes the more difficult part: currency conversions.
Which…turned out to be pretty easy!
Counting Money
In order to calculate currency, I first had GET conversion rates into the royalty report. I felt the best way to do this was to create a separate tab (as noted above).
This way, I can grab the exchange rates from Amazon each month and then update the historical averages with the exact rate for the month.
I had the macro populate historical exchange rates as I plan to give the macro away and some people may not want to do the effort of gathering exchange rates every month.
Almost all of the exchange rates were pretty stable with the exception of GBP–GBP was all over the map.
Then, within each “Currency Conversion” field, I used an IF formula on Excel that reads like this (modified it to help it read better):
GBP Conversion (example):
If Column M (Currency) = “GBP”, then multiply GBP exchange rate from Currency Conversion tab by the “Royalty” amount. If column does not equal “GBP”, do nothing and skip to the next row.
This equation will populate for every book that has a GBP currency, then it will copy over the converted currency in USD to a new column called “Adjusted Royalty.”
The “Adjusted Royalty” is the true number of what you sold in USD for the month. Give or take some rounding, it'll be pretty darn close to what you were paid if you add all the books up.
This video doesn't show the whole macro, but watch how the currency conversion works in Column S (again, this will all be automated in a few days—I'm manually typing in “GBP”).
The spreadsheet will do this for each currency AUTOMATICALLY, working methodically down the line until it is finished. Then I will have the macro DELETE the currency conversion columns since they aren't truly needed.
So what's left over will be almost everything that was on the original spreadsheet (just reformatted), and the adjusted royalty and adjusted currency columns.
Then I tied the currency conversion action to a second macro, and BOOM! Currency conversion problem is SOLVED!
Now the real test–will the report import properly?
Of course it does!
Now that this problem is solved, it's time to enter a tedious exercise: do the same thing for the old CreateSpace report, the KDP New Report, and the KENP Report.
Fortunately, the new report requires less massaging and won't take nearly as long because my “old report” macro essentially makes the old report look like the new one. Same with the print report. The KU one may be interesting though.
And next, it's time to format reports from all the other retailers: B&N, Kobo, Draft2Digital, etc.
That will take me some time, but probably not too long. I'll post again once they're ALL done. That way, you will really be able to see the progress, and then it'll be time to start building a front end for the sales data.
At that point, I'll almost be out of the woods and I will have solved a really, really big problem for myself (and hopefully other people).
