"Transaction Report" CSV details

[ducks2k]

I do my selling via the "reports" system of CSV files uploads/downloads and such.  MOSTLY, I can figure them out by trial and error, but it it more painful than it needs to be.  I've worked through the basic crap of having csv files that have descriptive lines before the field definitions - painful, but tolerable.  The long field names tend to be reasonably self-documenting, but nearly every one has nuances that need clarification.  As far as I can tell, there has been no new documentation in the last 5 years, although the structure has changed massively.  The old File Exchange documentation is still 90%+ relevant.

That said, my current issue is with the "Transactions Report" file.  The problem is that eBay's seller accounting system is too vague, and has been cobbled together over decades.  I am expecting a 1099-k (when?), but I can't find any documentation of how the numbers will be generated, and what they will "mean" from a tax standpoint, so I am trying to prepare by using the monthly "Financial Statement"s and the "Transactions Report" csv.  BTW - My monthly "Financial Statement" summary pdfs download just fine, but the "Full Statement" button does absolutely nothing.  Theoretically, I should be able to use the full statement to figure out which numbers contribute to the summary numbers.

The "Transactions Report" CSV

1.  Appears to contain all of the relevant detail of financial interactions between the seller and eBay.

2. The individual fields appear to have just grown, rather than having been designed.

3. eBay has a fundamental data problem in handling "orders" of one item differently than orders for multiple items.  This means that it takes a lot of data manipulation to assign order-level fees to each individual item sale. 

4. Promotional Sales fees are separate records, rather than a field within each relevant item record.  I can understand why they did it, but I don't have to like it.

5. "Other Fees" need to have a "fee code" or something to allow them to be grouped sensibly.  Using the description field for that purpose sort-of works, but not 100%. 

6. very unclear as to which dollar amounts get included into which fields for the different record types.  I'm sure there is logic to it, but that logic is not clear.

What I would  like to see in this thread are comments on discoveries about the "Transaction Report CSV file - what things users have been able to discover that are useful.  For example, I just discovered that I can obtain a list of the purchases I have made using the available funds feature which recently became available.

[mycheaperstuff]

I have learned to make various transformations to make the transaction report fit my needs. 

I don't have "discoveries",  just slogging through it and making lots notes using comments and working from copies of the base sheet before each significant transformations so I can back out if I need to.  Sounds like you are past it, but here are some starting steps I work with.

I delete or hide columns that don't affect me, like mattress waste fee.

Use edit replace to get rid of "--" fill for blank fields.

I use the item number as the unique identifier, not order number, of which there are several versions.

I use item number and sumif or pivot table to get all costs by item (including "other fees", as you say, very frustrating, for listing upgrades, two categories, etc.)

I also do sumif and pivot table by payout date to make sure all payments were made, and to eliminate from income any orders for which I did not receive payment in the current calendar year.

I add my own columns for the relevant subtotals because I want proof of whether sales tax is included in the 1099 and that FVF were calculated correctly.

Refunds are tricky. I think I selected on whether a record had an entry in the title field but a negative entry in the net amount.

[stainlessenginecovers]

My recommendation and what I've done since Managed Payments started:

1.) You can download the 'reports' by using a monthly date (12-1 to 12-31 for example) and download it via Excel and NOT CVS

2.) You can 'choose' what is shown on the report, downsizing the report (if you don't need the customers name, address, when you got paid, description etc. What I do is click OFF every box except 'Orders' and 'Refunds'

3.) I move the 'refund amounts to a positive number in the column next to 'Orders' and then change the negative in that column to Zero. 

4.) Add 1 row above the top one

5.) Title that 'TTL Gross' and the one next to it 'TTL Ref.

6.) Then, add the row bottom to top into that new 'empty' box-giving you the Total for the Month of Gross Orders and Refunds

7.) Use the Payments Tab and then 'Tax Report' for the month, print it and use that number for all FEES.

Now, I have Gross, Refunds, and Fees (I print shipping using Paypal, so I run a 'money sent' report for the month on Paypal to get that total.

Takes about 15 minutes at end of month to do this.

[stephenmorgan]

I have learned that there are 33 columns of data that explains everything about the sale and expenses with eBay.

Transaction creation date

Type

Order number

Buyer username

Buyer name

Ship to city

Ship to province/region/state

Ship to zip

Ship to country

Net amount

Payout currency

Payout date

Payout ID

Payout method

Payout status

Reason for hold

Item ID

Transaction ID

Item title

Custom label

Quantity

Item subtotal

Shipping and handling

Final Value Fee - fixed

Final Value Fee - variable

Very high "item not as described" fee

Below standard performance fee

International fee

Gross transaction amount

Transaction currency

Exchange rate

Reference ID

Description

[ducks2k]

The bigger question is what, PRECISELY, do each of those fields mean?  I can guess most of them, and about a third of them never occur in my sales.  The precise structure of the file/report is based on eBay's internal accounting structure, which they do not describe anywhere. 

For example, The promotional advertisement structure winds up with a separate "Other Fee" record for each item it applies to, rather than being a field in the order record like "Final Value Fee - variable". 

My stuff frequently sells with multiple items in an order, so the 30 cent FVF-fixed fee shows up in one of the random items in the order.  I think they have it right, but it makes it more difficult to resolve.

Other fields, such as "Custom Label" is called "SKU" in other places in their file/report system.  It would be nice if one of the fields in the upload was a private "item cost" field that could be carried like the SKU, but, at least, the SKU lets me link to my costs elsewhere.

My goal this week is to prepare for the 1099-k, and be able to process whatever number eBay decides to report to the IRS and assign appropriate costs to the revenue they report.

This week I have been able to assure myself that the transactions in this file have a 99%+ correspondance with the "Monthly Financial Statement".  I'm surprised it isn't 100%, but there may be rounding/truncation things going on.

Note that for 2022 - January-March Financial reports were for some semi-random accounting month, but then they converted to calendar month for the rest of the year.

[stainlessenginecovers]

Again; you can just order a Report with Orders and Refunds and don't bother with any of the others. Then, 'hide' what does show so there are 2 columns.

You can get all FEES on a Tax Report through Reports- which is issued monthly and in 1 simple page, shows ALL Fees you paid eBay for the month. 

[hardcorecollectors]

The area I'm struggling the most is why the reports do not allow me to group the dates. I'd like it by Month, Quarter, and Year but it will not allow me to group them! Gives me the error "can not group that data" Has anyone found a work around for it? I have tried many different fixes, but none have worked yet.

[stephenmorgan]

---

@hardcorecollectors wrote:

The area I'm struggling the most is why the reports do not allow me to group the dates. I'd like it by Month, Quarter, and Year but it will not allow me to group them! Gives me the error "can not group that data" Has anyone found a work around for it? I have tried many different fixes, but none have worked yet.

---

Not sure what you are having trouble with?

Many of the reports available from eBay you can specific the date range of what you want.  When I download a report it opens in a Spread Sheet Format and is in Date Order.  From the Spread Sheet is is easy to enter some blank spaces between ending of a month and beginning of the next month so the data will be grouped and can be separated into a new sheet if needed.

[desdinovamarie]

I hear you. I've had to make notes on the CVS file to keep it straight in my head - EX: column D=L-I-J-K  just to ensure nothing was deducted twice.

For tax filing at years end, I download eBay's CVS report by selecting that years specific date range. As others have suggested, I delete all unnecessary columns; sort the entire spreadsheet / data (using multiple levels of specific columns) such as by 'type' first, then date or whatever. Then enter a highlighted row under / between each of those types or categories.  I choose to sort in this fashion because at a glance, I want to make sure there are only 12 subscription fees, all the refunds are neatly grouped, etc. Then I add my formulas for each column into the highlighted rows.  Sometimes I need to back up and do a second 'sort' to better define the data. If you're using TurboTax, most of these fees and charges will be deducted under Business Income & Expenses - Miscellaneous Expenses. Might seem to be more work breaking it down this way but to me it simplifies the data and extracts what I need.