I'm back on the progress train, which is a beautiful thing after where I was a few days ago. I'm humming along with getting royalties to display properly.
But first, click here to read the first part of the series if you're new here.
To recap from the last post, I need to build an architecture that looks like this:
I started by creating a query for each of the ebook and paperback tables:
Then I created the condensed query, which looked like this (two screenshots):
All calculations and royalties on the screen are 100% correct, even though the fields are a little messy. Exactly what I want, though not what I had in my mind's eye, admittedly.
This is really good stuff—2 days ago I wasn't sure if I'd ever get to this point!!!
This is the hallelujah picture–the picture that I never thought I'd see. It's got royalties broken down by marketplace and retailer. Very, very easy to run queries on this now:
With the image above, I can easily layer in audio, translation, and other format types as needed.
Long story short, here's what I had to do to get this.
Nobody ever said this would be easy. I'll have gray hairs about 2 years sooner now, but at least I figured it out.
Loading in More Data (Carefully)
Time to scale up a bit to see how everything does.
Assuming I import more Amazon ebook and paper royalty statements into Access correctly, all of the queries I created SHOULD update automatically. Will they, and will the math be right?
Let's find out…
My friends, 3 months of royalties took about 15 minutes to import, and they went in as smooth as butter:
Very clean import, and all accurate. As you can see, now I've got some meat on this table!
Why did it take 15 minutes?
To reiterate, these are 2014 KDP “Old Reports”, so I have to massage the data to get it into Access clean. Namely, I have to add some columns, delete a bunch of rows, and change the column order so that it matches the Access table EXACTLY.
Why do I have to do all that? Because I'm syncing up the “old reports” to match the column format of the “new reports”. It's more work for me, and I don't have to do it, but it'll ensure that when I get to Oct 2016 (the date the new reports started), I can just slide in the reports with almost no massaging at all.
Now that I've build a solid foundation that I can scale, now it's time to tackle the problem of conversion rates.
You Say Dollar, I Say Pound
Amazon lists the exchange rate it uses to convert currency in its “Payment Report.” Authors are usually paid somewhere between the 26th and the 29th of every month, usually around the 28th. Other retailers are different, but the concept is the same.
However, what I wasn't able to figure out is the exact date that exchange rates are calculated…or the bank Amazon uses (which I assume is Synchrony, but they don't publish historical exchange rates).
And then things got really, really complicated.
Currency exchange rates can be grouped into two categories:
- Historical conversion rates, which are easy to find, and relatively consistent.
- Current conversion rates, which are always in flux, and don't become historical until around 90-120 days. Conversion rates can change by the HOUR.
Historical conversion rates are easy to find. In fact, there's even an API that will give me all of that information directly into Excel, which I can link to Access. That's not really a problem.
The problem is that only 1 retailer I know of lists the actual currency conversion rate in their royalty statements (Kobo). Here's how everyone functions:
- Amazon lists the exchange rate in their Payment report, and conversion is applied at the global level (meaning against your monthly payment for all your books, so you would have to calculate the conversion rate for each of your book sales separately, creating rounding problems)
- Barnes & Noble doesn't even give you foreign sale information. If I remember correctly, they did sell books in the UK at one point, but it's hard to know how those registered on a royalty report.
- Draft2Digital doesn't report exchange information, but they do provide the currency. and estimated USD sales. Again, hard to know what date they're calculating this from, but it does make the royalty statement easier to read.
- PublishDrive does not provide exchange rate information–only what you get paid in USD.
- Smashwords does not provide this information.
- Google Play's report is perhaps the most confusing of the bunch—the royalty report ONLY shows the foreign currency…and you get paid in USD.
- Kobo lists the exchange rate per book, which in my opinion, is the correct way to do it (thanks Kobo for getting this right!)
If I break this down, I ONLY have to do exchange rate calculations on Amazon and Google because I know that the other retailers are going to list the USD I get paid in on the royalty statement (or at least an approximation). But if I want more accurate data, I HAVE to convert currencies for at least Amazon and Google.
The fact that THESE 2 retailers DON'T LIST THE AMOUNT YOU ARE PAID IN USD ON THEIR ROYALTY STATEMENTS is problematic. It means that even if I don't want to, I HAVE to do a currency conversion, and the only way to do it is to look at my payment report and hope that foreign countries are delineated on the report (yes for Amazon, no for Google).
Going back to Amazon, the next problem is that Amazon's exchange rates don't line up with historical rates, which means using an API is useless, or I'll have to settle for inaccurate information…or manually enter and calculate the exchange rates every month (at least for now, on the old reports)/
See why this is complicated?
When faced with a complicated situation, the best answer sometimes is the simplest one.
My North Star: As Close to 100% Accuracy as Possible
I won't settle for the easy route, which is an approximation of royalties. I could do that in just a couple hours.
As a business owner and author, I owe myself better than that.
My goal is to shoot for 100% accuracy with my royalty statements knowing that I'm going to fall short. I'm better with falling short than not trying.
That means I'm going to take the time to build this right.
Here's what I think this should look like.
In case that image is too small:
Step 1: Capture the most current exchange rate from Amazon KDP
- Manually or
- via an API (not likely)
Step 2: Format the KDP Royalty Report using a quick Excel macro (very, very easy)
Step 3: Run a currency conversion macro that takes the most recent conversion rates and applies them to the values on the royalty statement (could either be part of the macro in the last step, or a separate one). This macro would also finalize the formatting and get the spreadsheet Access-ready.
Anyhoo, this requires me to read up on Excel VBA, which isn't terribly hard, but I need to figure out what I can and can't do.
That's the battle I'll be fighting next. Don't be surprised if it takes me a couple of days. I appreciate you following this far!
