I had a thought that maybe there was a way to make updating my cumulative title sales spreadsheet easy enough that it would be worth keeping it up to date so I revisited the spreadsheet today for about an hour or so.
As you can see in the image above, I have several sheets in the workbook. The thing is, I know just enough Excel to know how much I don’t know.
But I had a thought and I followed up on it and that thought led to another and suddenly I’d found a way to make the currency conversions for the KDP sheet easily and quickly—and in a way that would work for future additions to the KDP sheet without requiring any additional effort on my part.
You see, if you’ve seen the KDP reports from Amazon, you’ve probably noticed they don’t include the currency conversion for the payments the way the reports from everywhere else does. It’s an inconvenience, but it is what it is.
My challenge was to find a way to prorate each month’s payment to each month’s sales. The way I chose to do it a few days ago was inelegant and not wholly accurate for each book. That bugged me, but it was a time versus accuracy dilemma, and my need to save time and effort won out.
But now I’ve figured out how to do it with zero additional upkeep.
#1
I created a column for a conversion factor in my book sales report where I track royalties earned and royalties paid by month for each vendor.
Sample row data (extraneous columns deleted):
| Oct 2016 |
Amazon.de |
EUR |
36.87 |
38.43 |
1.0423108 |
The conversion factor is simply the payment amount, which I have a column for ÷ earnings in the original currency, which I also already have a column for.
#2
I multiplied the foreign currency royalties in the KDP sheet by the conversion factor and got the USD royalties for each item (which I’d consolidated from the monthly KDP reports). I did this for all the rows in the KDP sheet.
If I’d had to do it manually, it would have taken forever.
The trick with the formula was to use the SUMIFS function because my royalty payments spreadsheet has only one row for every date and marketplace combination. That means the sum is always going to return only the conversion factor that matches the date and marketplace that the formula needs to calculate the USD royalty. It was just good luck that since KDP reports don’t show transaction dates, I had set up all my rows in the KDP sheet to contain only the period end date, which is also how I’d set up my royalty payments sheet (Oct 2016 is actually 10/31/2016 but set to the MMMYYYY format).
#3
I created a table so I could use the VLOOKUP function to solve the problem of having different names for the marketplaces in the two sheets (Amazon Kindle US Store vs. Amazon.com) but that was actually the easy part. :)
Anyway, what this means is that my brain spent a lot of time working on this while I wasn’t actively working on it—even after I’d decided it was too much trouble to keep up. But that happens sometimes.
I’m happy to say the sheet works well with the new formulas, even if my formulas are a bit of a hack. Updating the cumulative title sales will be a simple process that shouldn’t take more than half an hour every few months, and it’ll have the actual payment amounts from KDP applied to the correct months’ foreign sales.
I’m glad I was able to come up with a solution, and I swear, it feels like it’s taken me longer to write this post than it took me to fix the spreadsheet.
I had considered the idea of one massive spreadsheet that I could put into a PivotTable, but I didn’t consider that a good long term solution. The data between reports just isn’t standardized enough and I sure didn’t want to spend a bunch of time standardizing it each time I decided to update the cumulative title sales report. And I’m pretty happy with the solution I’ve come up with, because it means I can just copy and paste the new data into the appropriate sheets and I’ll be good to go.
Smashwords might be tricky, but my current plan is to just delete and re-add all the data from the updated reports, instead of append it. This is necessary because Smashwords throws in new transactions for some very old periods sometimes and trying to pick those transactions out of the whole would be a waste of time.
I don’t figure I’ll update this spreadsheet more than once a quarter anyway, but I’m tickled that it’ll be maintainable without a lot of effort. :D
Now, off to do some other stuff before I call it a night. It’s 12:03 a.m. and I swore I wasn’t going to bed late again! :o