Amazon Report Manager, Help and Download

ARM Download and Help

by J. L. Doty

Contents

Getting Started

Basic Operations

Consolidated Worksheets

A Note on Titles

Configuring ARM

Prerequisites

Create a Consolidated Worksheet

Update a Consolidated Worksheet

Duplicate File Check

Duplicate Entry Check

Configure Title and Series Abbreviations

Currency Conversions

Marketplace Abbreviations

Summary Reports

Summarize Units or Royalties

Summary Report Charts

Net Units by Series YTD

Net Units by Marketplace 2019–2020

Royalties by Title, USD, YTD

Royalties by Marketplace, USD, YTD

Royalties by Marketplace, Indian Rupees

Getting Started

Note: these instructions are based on Excel 2010 on a PC, and Excel 2011 on the Macintosh, and have been tested on Windows 10 and Mac OS X El Capitan. Amazon Report Manager (ARM) may or may not work with later releases of Excel, but it hasn’t been tested on them.

Warning!

Visual Basic for Applications (VBA) is notoriously unstable. The results produced here have been extensively tested for accuracy, but VBA can crash sporadically. Crashes occur most often when Excel has been running for a time and a lot of calculations have been performed. When it crashes and you get an error message, the first thing to do is shut down all instances of Excel, then reopen it and try again. That frequently clears up problems.

To get started with Amazon Report Manager (ARM), download the ARM Spreadsheet here and

place it in a folder by itself.
In the same folder, create a sub-folder titled KDP Reports, and put all your Prior Months’ Royalties reports there.

When you attempt to open it, if all goes well:

on the PC, the spreadsheet will have a tab in the ribbon titled Amazon Report Manager that looks like this:

Click image to view it full size.

on the Mac, the Excel menu bar at the top of the screen will have an Amazon Report Manager menu item and sub-menus that look like this:

Click image to view it full size.

Note: in the interest of simplicity only the PC tab items, and not the Mac menu items, will be referred to going forward. But they do work the same.

On the other hand, if, on a PC:

you get a warning that macros are disabled, or
there is no Amazon Report Manager tab visible on the far right of the ribbon, or
the ARM tab is visible, but when you click on it, and click on Amazon Reports in the Consolidate group, nothing happens,

do the following:

click the File Tab
click Options on the lower left
in the left pane of the dialog that opens, select Trust Center
on the right of the dialog, click on the button Trust Center Settings . . .
in the left pane of the dialog that opens, select Macro Settings
click on the radio button Disable all macros with notification
then click OK twice to close all dialogs, and exit Excel.

The above should not be necessary for the Mac, but you may have to respond to a dialog and click a button to Enable Macros.

Open ARM again, and if you get a warning beneath the ribbon that “Macros have been disabled,” click on Enable Content button. You should be good to go.

Basic Operations

Consolidated Worksheets

To simplify calculations, ARM consolidates the Prior Months’ Royalties files into a single consolidated worksheet that looks like this:

Click image to view it full size.

Note the Series Abbreviation and Title Abbreviation columns. You must provide these abbreviations to ARM; more on that later. Also note the large numbers for Units Sold when the Unit Type is “KENP.” The actual unit for Kindle Unlimited is individual Page Reads. For ARM’s reports to make sense of this, you must provide the Kindle Edition Normalized Page Count (KENPC) for each title enrolled in Kindle Unlimited; more on that later.

Note that you only need to provide this information in a table in the Titles worksheet, and moving forward ARM ensures that the abbreviations are properly entered in the consolidated worksheet.

In the above example, notice the (*) in the Royalties column for entries of type KENP. That indicates that the report for that month was downloaded before the KENP rate had been determined by Amazon. In that case, ARM used the KENP rate from the prior month, which might produce some small error, but not much.

ARM also provides tools to ensure that the information you need to provide is complete. These tools are integrated into the process of creating a consolidated worksheet. For example, when consolidating a worksheet, if ARM encounters a title for which it does not have an abbreviation, it will flag it, warn you, and create a blank entry in the Titles worksheet to which you need to add the abbreviation for that title. Because of this, a simplified way of configuring ARM is to:

Create a consolidated worksheet without making any entries in the Titles worksheet.
Let ARM make blank entries of full titles without abbreviations.
Edit the Titles worksheet to add Series and Title abbreviations, and KENPCs.
Throw away the previously consolidated worksheet and recreate it so ARM can properly fill out the abbreviations columns.

It is from a consolidated worksheet that ARM will then produce report tables and charts of things like Royalties by Series over a given time span.

A Note on Titles

Amazon is very consistent in being completely inconsistent in how it reports on a title. In its reports the title might be listed as:

just the title, or
title + subtitle
title + series title
and any other possible variation one might think of.

And it will vary back and forth between these from one month to the next. Below is a Title Analysis of my consolidated worksheet as of September 2021.

Click image to view it full size.

Notice the way the same book is reported with several variations on the title. ARM handles all of this easily through the title and series abbreviations.

Configuring ARM

Prerequisites

Remember that you should never delete the Titles or Currencies worksheets. But if you do, simply re-download the worksheet and re-configure it.

It is assumed that all the necessary spreadsheets have been downloaded from the Prior Months’ Royalties link on the Amazon Reports web page and stored in a single folder named KDP Reports located in the same folder as AmazonReportsManager.xlsm.

Create a Consolidated Worksheet

First create a consolidated worksheet of Amazon Reports:

click on the Amazon Report Manager Tab
click on Amazon Reports in the Consolidate group

The Consolidate Report Files dialog will open, and look similar to this:

Click image to view it full size.

If no files appear in the list then there are no Amazon Reports in the KDP Reports sub-folder. Click Browse and select the folder where your Amazon Reports are stored.

Note that ARM defaults to the KDP Reports sub-folder to get Amazon Report files, so you can save yourself the Browse step by placing all reports there.

Select the files you want to consolidate and click Ok. ARM will create a worksheet of consolidated Amazon reports with a name something like Sep 10 2021 Cons; basically the date created plus the suffix “Cons.” And if a worksheet by that name already exists, it will append “-X”, where X is an integer number 1, 2, 3, etc..

Note that ARM does not modify the original report files in any way.

Also note that Visual Basic for Applications (VBA) is notoriously unstable. If you’re consolidating a lot of files at once and it crashes, close the application, reopen it, delete any half-completed consolidated worksheets, and try again.

Update a Consolidated Worksheet

If you have already consolidated a lot of files into a worksheet, select that worksheet in the dropdown list and check the box for Update Selected Sheet. ARM will automatically select the new files not included in that worksheet. You can then click Ok, and it will update the sheet much faster and with a lot fewer calculations, creating a new consolidated sheet in the process. At that point you can delete the old consolidated sheet.

If you’re trying to consolidate a lot of sheets and running into the aforementioned VBA instability, try consolidating a smaller number of files, then close the application, and use the update method to add files in smaller chunks to the consolidation.

Duplicate File Check

When consolidating files, if ARM detects duplicate files for a given month, it will note that in the list on the right in the Consolidate Report Files dialog, as shown above. You can still include both files in the consolidation, but that will probably result in duplicate entries, which will be flagged in the Duplicate Entry Check.

Duplicate Entry Check

After creating a consolidated report worksheet, ARM scans all entries in the new worksheet and attempts to ensure that there are no duplicates. If it finds any, ARM issues a warning and offers you the opportunity to have the duplicates removed from the consolidated sheet. In the dialog box that appears, if you click Yes, the duplicates will be removed from the consolidated sheet and placed in a separate sheet with the suffix “-dups” appended to its name.

Note that the duplicates check can be fooled. For best results, make sure the individual monthly royalty report spreadsheets have been downloaded, and have not been modified or duplicated in any way.

Configure Title and Series Abbreviations

The consolidated worksheet can now be used to make summary tables, but first it will be convenient to create abbreviations for the titles of your books, and any series to which they belong. Without abbreviations, a summary report will look something like this:

Notice how the headings for each column are the full title of each book. Without specified abbreviations, ARM has no choice but to use the full title name. This situation will be aggravated by the inconsistent way Amazon reports titles, as discussed in A Note on Titles. Because of those variations, you can end up with multiple columns for the same title. But with abbreviations, the same report can look like this:

This is much more readable and compact. As an example of the abbreviations I’ve set up for my books, I have a 4-book epic fantasy series titled The Gods Within. The four books in the series are titled:

Child of the Sword
The SteelMaster of Indwallin
The Heart of the Sands
The Name of the Sword

I gave them the abbreviations Gods 1, Gods 2, Gods 3, and Gods 4, with an abbreviation of Gods Box for the 4-book boxed set, and Gods for the series. Simple numbered abbreviations like this help considerably when sorting alphabetically.

To create abbreviations, you could enter the titles of all your books into the Titles worksheet, but a single typo will prevent the abbreviation from working properly for that title, and you’d have to account for all the possible inconsistent variations that Amazon produces in the reports. So let ARM handle it for you. On the ARM tab, in the Analyze group, click on Titles to open the following dialog:

Click image to view it full size.

This is a summary of all the titles that appear in the worksheet selected in the Source Worksheet combo box. In this case the author has already entered abbreviations for a number of books, but has recently introduced a new series that don’t have abbreviations, and they are obvious in the above dialog box.

Note that when creating a consolidated worksheet, if titles are encountered that don’t have abbreviations, a warning dialog appears, and you’re given the option to perform the same function as hitting the Add button, as described below.

The Add button at the bottom only appears if there are titles present in the source worksheet that aren’t present in the Title Abbreviations section of the Titles worksheet. Click it, then close the dialog and go to the Titles worksheet, which now looks something like this:

Click image to view it full size.

Now you can enter abbreviations and KENPC values. But notice the way many of the new titles are duplicates with variations mentioned previously in A Note on Titles. ARM is smarter than that and only needs a unique title prefix. You can simplify the process and alleviate future headaches by removing the duplicates and editing the titles down so it looks something like this:

Click image to view it full size.

Finish by deleting the consolidated worksheet and recreating it.

Keep in mind that as you publish new works, you can repeat this procedure to add the new title abbreviations to the Titles worksheet. And ARM only adds new titles not already there, so you don’t need to repeat your previous entries.

Note that you can rearrange the order of the titles in the above worksheet. ARM doesn’t care what order they’re in. You can also delete any rows containing unwanted titles that are no longer valid. But ARM stops searching the above table when it encounters a blank cell in the Title (unique prefix) column, so no blank lines. The titles you wish to retain must be in a continuous list immediately below the table’s heading row with the colored background fill.

Currency Conversions

The Currencies worksheet is a list of currency conversions by date, and relative to the US Dollar. ARM uses this table to convert all currencies to a single desired currency chosen by the user.

Note that it’s up to the user to update exchange rates regularly, since they do fluctuate. The user also has the option of changing the Friendly Names. However, ARM uses the last available currency in the table regardless of the date of the royalty. So unless there have been significant currency fluctuations since then, the conversions will still be reasonably accurate.

Other than updating exchange rates, there is no reason for the user to be concerned with the Currencies worksheet since it contains exchange rates for all of Amazon’s existing markets as of September 2021. However, should a problem arise, the Currencies button in the Analyze group can be used just like the Titles button to ensure that all exchange rates required for conversion are present. Like the Titles button it will analyze the currencies in the consolidated worksheet, display a similar dialog with all the currencies that don’t have an exchange rate entered in the Conversions worksheet, and give the user the opportunity to add them automatically.

If, for some reason, any currencies are missing from the table when a summary is created (see Summary Reports), a warning is issued to make the user aware of any errors.

The exchange rates in the table should always be those required to convert all currencies to US dollars. This does not prevent the user from creating Royalty Reports in any currency desired, but it simplifies the computational algorithms.

Marketplace Abbreviations

ARM does not need entries in the conversions worksheet to convert Marketplace entries to a more compact format, but instead uses a simple algorithm to parse abbreviations from the Amazon style Marketplace entries in the consolidated worksheet.

All of the Marketplaces listed in the consolidated worksheet, and their computed abbreviations, can be viewed by clicking on the Marketplaces button in the Analyze group. A dialog box pops up similar to this:

Click image to view it full size.

Summary Reports

Summarize Units or Royalties

This is the meat of ARM. Once you’ve created a consolidated report worksheet in ARM, and ensured that it has entries for all abbreviations and KENPCs, click on Units or Royalties in the Summarize group on the ARM tab, and the following dialog will pop up:

Click image to view it full size.

This dialog provides the following options:

If you’ve created more than one consolidated report worksheet, you can choose which to use as the source by selecting it in the Source Worksheet combo box.
In the Currency group you can
- select a currency in the small combo box to which all royalties will be converted in the report, and
- set the selected currency as the default for future use.
In the Summarize group you can
- choose to summarize Unit Sales or Royalties,
- and if Units is selected, you can exclude free giveaways.
In the Summarize xxxxxxxx by group you can
- choose to summarize by Title, Marketplace, Series, Unit Type, or Transaction Type.
- Exclude titles with no sales, and
- Break out Unit Types: eBook, Paper, Hard, and KENP.
In the KU KENP Units group you can have KENP units listed as raw page counts, or the number of full eBooks read; the latter is computed by dividing the raw page counts by the KENPC value entered for each title in the Titles worksheet.
In the Date Range group you can
- use the spin buttons to select a sub-range of the months available in the consolidated report worksheet,
- use the Reset button to reset the dates to the maximum range available in the selected Source Worksheet,
- use the radio buttons to select whether the spin buttons increment dates by 1 month or 1 year, and
- click the YTD button to set the date range from January to the present month of the present year.

Summary Report Charts

When the Ok button is clicked in the Summarize dialog, ARM generates summary sheets as detailed in the examples that follow. But ARM also automatically generates two charts in each summary report sheet:

it generates a chart of the individual details as a function of month, and
it generates a chart of the Total Unit Sales or Royalties as a function of month.

It generates the two charts separately because the totals often swamp the details of the individual columns. The user can expand the charts, modify them, or delete individual series to get greater insight into sales. You can also double-click on the numbers on an axis to open the Axis Options dialog box and change the scale.

Following this are examples of the four types of reports with charts that can be generated.

Net Units by Series YTD