The rubber finally meets the road.
One author who just learned VBA four days ago…versus complicated royalty statements!!!
Ready, FIGHT!
But before you watch the brawl, click here to read Part 1 of this series if you're new. (Totally anti-climactic, I know).
This was a battle of endurance, let me tell you. I'm shocked that I had the ability to figure this out, honestly, because if you ever see me use Excel, I'm the equivalent of the person who types with one finger. But I did it.
This was like playing Super Punch Out 2. Beat one opponent, move on to the next…
Round 1: Amazon KDP Reports
Amazon has perhaps the most robust royalty reports. They are also amazingly complicated from a formatting perspective.
There are 4 different reports:
- The “old” ebook royalty report that was in production from the beginning of the KDP program to October 2016
- The “new” combined ebook, paperback AND KENP report that was available sometime around late 2018 (different people got the KDP Print dashboard at different times)
- The CreateSpace royalty reports, which were reformatted to match the KDP Print report. For our purposes, the CreateSpace and the KDP Print Report are exactly the same, but the difference is that the KDP Print report is nested inside the “new report” along with ebook and KENP sales.
- The KENP report, which is included as a tab in the “new” report
I started with the “old” report, which I figured would take the longest. It took me almost 2 days. But I got the macro to format the report—then you manually have enter the currency next to each transaction to trigger the currency converter.
It is what it is. Frankly, if you consider what a mess of a report it is, it's amazing I was able to do anything at all.
Once I had the “old” report formatted, the other 3 fell quickly as I had done most of the hard work.
Round 2: Barnes & Noble
The B&N reports are so simple I love it. It doesn't get any easier than this!
I wrote a macro in less than 10 minutes. Next.
Round 3: ACX
ACX reports look scary from a macro perspective, but they're actually not that difficult. I just had to break up the formatting and separate A La Carte (ALC), Audible Listener/Credit (AL), and Listener Plan/Not Credits (ALOP) into their own separate tabs and import each one separately.
Writing a macro was straightforward, but because of some of the formatting on the spreadsheet, my macro commands wouldn't allow me to delete rows containing totals OR adding columns.
I'll probably have to cry uncle on this one and get someone to write a quick line of code to help me. Still a victory, though I'm bruised…
Onward…
Round 4: Kobo
Kobo's report is like B&N–easy and straightforward.
A ironic fact about Kobo: they didn't even have royalty reports until a couple of years ago. It was one of the biggest downsides to the platform, but they've rectified it.
I macro-ized the Kobo report in about 10 minutes.
Round 5: Draft2Digital
Draft2Digital offers two different reports: one with raw data that gives you the ESTIMATED USD you'll be paid, and an official royalty report with the exact amounts.
The raw report is very simple and easy to macro-ize. The royalty statement is less so. I opted for the raw report vs. the royalty statement since the estimate of USD is usually pretty accurate. I'm OK being slightly off in the name of efficiency.
Round 6: PublishDrive
PublishDrive's royalty report is similar to Kobo's. It fell in about 5 minutes.
Round 6: Google Play
POW!
I opened up Google Play's royalty report and got punched in the face.
When I got my bearings, the report pointed at me and said “Come and get it.”
But I didn't want to because…good lord, Google Play's royalty reports are a mess.
BUT…!
I realized that I misspoke about Google Play in earlier posts. Their SALES REPORT does NOT contain currency conversion, but their Earnings Report does!
So I took a deep breath, ran at Google…and macro-ized the earnings report in about 10 minutes.
Round 7: Findaway Voices
I sell a lot of audiobooks on Findaway Voices, so this report was especially important to me.
5 minutes later, I had a macro…
“Jim, this Michael La Ronn guy is an animal in the ring. He's better than Mike Tyson!”
Round 8: Smashwords
And, we have Smashwords. I've sold maybe 10 books there since 2014, but hey, they need some loving, too.
Macro was done in 5 minutes.
Honorable Challenger: Apple Direct
I don't sell books on Apple Direct so I wasn't able to do a macro. However, if you happen to have a royalty statement you'd like to share, feel free to send it my way (with amounts removed of course) and I'd be happy to make a macro for the community.
Ladies and Gentlemen, We Have a Champion!
In just 3 short days, I whipped all of my royalty statements into shape.
I can now import every royalty report I receive directly into Access with the click of a button.
This now means:
- I can run reports and get insights into my sales unlike anything I've ever done before. Going to be very, very, very powerful.
- I can start tying sales to books in my database, and I can get a full picture of sales along with everything else for the book
- I can run always-up-to-date profit and losses per book
- I've created a Swiss-army knife that will be a (publicly-shared) secret weapon for me in the future.
I can now manage my books, updates to my books, sales, and even expenses in the same database.
I can ask this database virtually ANY QUESTION and get an answer in seconds.
I know this isn't sexy, but it's POWERFUL.
In the next post, I'll share my progress on building a front-end for looking at my sales report data, and how I am linking them to the work I've already shown you for my book profiles.
I'm in the final stretch and if all goes according to plan, should be done with a working model of my database by end of the week.
Don't forget to join my Fan Club! Thanks for reading.
