Calculating balance with my downloaded PayPal transaction history

Question

PayPal lets you download every transaction in your account, since the account was opened, to a CSV file for you process as you wish.

I wish to determine the actual account balance.

I have imported my entire PayPal into a single SQL database table so I can run queries against it to perform calculations and explore the data. There is too much data for Excel anyway.

The schema of my database table contains all of the fields in the CSV file, but these fields in particular stand-out:

DateTime,
Type,
Status,
Gross,
Fee,
Net,
TransactionId,
ReferenceTransactionId

Unfortunately, PayPal does not provide any specification document that explains the significance of certain values (Transaction CSVs are different to PayPal's IPNs).

My first observation with the data is that PayPal rewrites history!. One would assume PayPal would function like a bank in that transactions are immutable once written to the log: for example, if $50 is added to your bank account but then a few days later the transaction is reversed/refunded, you would expect to see:

Date         Amount     Description
2016-01-04    50.00     $50 deposit
2016-01-07   -50.00     $50 deposit refunded

If you do a SUM(Amount) you will get 0.00 - because these two transactions resulted in a net zero.

However, with PayPal the experience is this:

1. Step 1: Download transactions CSV for January

2. Step 2: The CSV file will contain the following (paraphrased):

   Date         Amount     Description    Status
   2016-01-04   50.00      $50 deposit    Completed
   

3. Step 3: Re-download the transactions CSV for January a few days later

4. Step 4: Observe the CSV file will still contain only 1 row, but look like this:

   Date         Amount     Description    Status
   2016-01-04   50.00      $50 deposit    Canceled
   

...which means you need to always re-download history going back at least a year to catch any rewritten transactions and reimport them into your database table.

You can argue that it isn't a problem - just perform a SUM where Status <> 'Canceled' - and that probably is the right solution, except...

• I've identified over 47 different unique combinations of Type and Status values, making it more difficult to determine which of those should be included in the SUM or not:

  +-----------------------------------------+-----------+
  | Type                                    | Status    |
  +-----------------------------------------+-----------+
  | Add Funds from a Bank Account           | Completed |
  | Authorization                           | Completed |
  | Cancelled Fee                           | Completed |
  | Chargeback Reimbursement                | Completed |
  | Chargeback Settlement                   | Completed |
  | Donation Received                       | Canceled  |
  | Donation Received                       | Cleared   |
  | Donation Received                       | Completed |
  | Donation Received                       | Refunded  |
  | Donation Received                       | Reversed  |
  | Donation Received                       | Uncleared |
  | Donation Sent                           | Completed |
  | eCheck Sent                             | Cleared   |
  | Express Checkout Payment Sent           | Completed |
  | Mobile Payment Received                 | Completed |
  | Payment Received                        | Completed |
  | Payment Review                          | Cleared   |
  | Payment Review                          | Placed    |
  | Payment Sent                            | Completed |
  | Payment Sent                            | Refunded  |
  | PayPal card confirmation refund         | Completed |
  | Recurring Payment Received              | Completed |
  | Recurring Payment Received              | Refunded  |
  | Refund                                  | Completed |
  | Request Received                        | Canceled  |
  | Request Received                        | Pending   |
  | Reversal                                | Completed |
  | Shopping Cart Payment Sent              | Completed |
  | Temporary Hold                          | Placed    |
  | Temporary Hold                          | Removed   |
  | Update to Add Funds from a Bank Account | Completed |
  | Update to eCheck Received               | Canceled  |
  | Update to eCheck Received               | Cleared   |
  | Update to eCheck Received               | Updated   |
  | Update to eCheck Sent                   | Cleared   |
  | Update to Reversal                      | Canceled  |
  | Web Accept Payment Received             | Canceled  |
  | Web Accept Payment Received             | Cleared   |
  | Web Accept Payment Received             | Completed |
  | Web Accept Payment Received             | Held      |
  | Web Accept Payment Received             | Refunded  |
  | Web Accept Payment Received             | Reversed  |
  | Web Accept Payment Received             | Uncleared |
  | Web Accept Payment Sent                 | Completed |
  | Withdraw Funds to a Bank Account        | Completed |
  | Withdraw Funds to a Bank Account        | Pending   |
  | Withdraw Funds to Bank Account          | Completed |
  +-----------------------------------------+-----------+
  

• PayPal will rewrite some transactions, but other times they will append a related transaction to your history (pointing to the original transaction in the ReferenceTxnId) - and sometimes they will do both.

• And the ReferenceTxnId field is used to represent a multi-layer hierarchy of transactions - that is, purchase transaction 1 could have a related refund transaction 2, and transaction 2 could have a counter-refund transaction 3 that reference 2 and not 1).
  • I had to write a recursive CTE query to retrieve the "transaction tree" for a given root transaction.

Other curiosities I've observed include:

• PayPal using the Name column (intended for the customer's name) to sometimes store transaction type information, and the From Email column (the customer's email address) to contain PayPal's own email addresses, like [email protected].
• Unexplained and apparent inconsistent use of:
  • "Canceled", vs "Refunded", vs "Reversed", vs "Removed"
  • "Completed", vs "Cleared", vs "Placed"
• And what is the difference between:
  • Type: Web Accept Payment Received, Status: Refunded
  • ...and...
  • Type: Refund, Status: Completed

...why is PayPal being so obtuse?

Here's a real-world example of a series of transactions I imported. My question to you, the reader, is that assuming these are the only transactions in an account, what is the final PayPal account balance?

DateTimeUtc           Name                         Type                            Status       Gross     Fee      Net      FromEmail              TxnId     ReferenceTxnId
------------------    -------------------------    ----------------------------    ----------   -----    -----    -----     --------------------   ----      --------------
2014-06-15 17:47:30   (Customer name)              Web Accept Payment Received     Completed    20.00     -1.05    18.95    [email protected]   1111          
2014-07-02 03:41:22   (Customer name)              Temporary Hold                  Removed     -20.00      1.05   -18.95    [email protected]   2222      1111
2014-07-16 21:00:58   Reversal                     Update to Reversal              Canceled     18.95      0.00    18.95    [email protected]   3333      2222
2014-07-16 21:00:59   Chargeback Settlement        Chargeback Settlement           Completed   -20.00    -20.00   -40.00    [email protected]   4444      1111
2014-10-01 13:32:55   Chargeback Reimbursement     Chargeback Reimbursement        Completed    20.00      0.00    20.00    [email protected]   5555      1111

Depending on how you interpret it, it could be:

• - 1.05
• 17.90
• -20.00

...and I have no idea what the right answer is myself!

Answer 1

I have encountered this too, and nearly every month the downloaded transactions do not match up with the statement balances. In my experience it isn't necessarily because of transactions changing later; I have seen discrepancies for the following reasons:

1. When a refund is processed the fixed fee portion is not refundable, and this is not included in the downloaded transaction. In your example of a $20 refund, $0.05 of the $1.05 is the fixed fee portion and is not returned to you, so your true balance will be off by 5 cents. (I have no idea why the true amount is not reflected in the downloaded transaction. It is correct on the itemized statement though.)
2. When a customer files a dispute with their credit card provider, PayPal will contact you and let you know about it, and there is an option to approve the refund. If you approve it PayPal will refund the money and also charge you $20, and that fee will not show up on your downloaded transactions. (At least it didn't for me the one time it happened.) BTW, instead of approving the refund for the CC dispute, if it was paid to you in the last 60 days you should be able to just refund the transaction the normal way and avoid the $20 fee. (Why people ever dispute a charge with their CC company rather than just emailing customer support and asking for a refund first is beyond me...)

So what to do about it? So far I've been manually reconciling with the itemized statement each month. For me refunds are rare so the differences are easy to spot.

Side Note: I see you are using the micropayments fee schedule. Since you don't have the default I'm sure you know that PayPal offers 2 different schedules:

1. $0.30 + 2.9% (normal)
2. $0.05 + 5% (micropayments)

Some quick algebra tells us that if your average item sold is more than $11.90, then you are better off with option 1. You are apparently using option 2 even though all of the charges in your example are $20 or more. If your example numbers are indicative of your average sale, you could probably save some money by changing to option 1. Note that the reason I say probably rather than definitely is because when you have refunds you don't get the fixed fee back. So right now you only lose 5 cents per refund, whereas you would lose 30 cents per refund if you switch. You'll have to run the numbers for your average sale price and consider your refund rate to know which is best for you.

Answer 2

Great effort on the question.

All I can advise is: If paypal does not provide the mapping table (Type, Status, Credit/Debit) you will have to build it up yourself as you go along.

I would tackle it this way: Your SQL mapping table has Credit/Debit entries for all knows combinations and the Credit/Debit entry would be -1 for all. Then, as you get a list of transactions you will have to display all possible results to the user who will then tick the correct one (comparing it to the balance). You would store the Credit/Debit entry if it is unique. This way you will learn what the possible combinations are over time.

Its a to the nth problem, so maybe start with a small number of transactions first. I expect you can build up pretty quickly.

It will be an interesting experiment...

Answer 3

I recently needed to compute a better balance that let us pick and choose what to include in the computed sum without losing information, so I revisited this topic and I'm pleased to say that I've found a solution that works (at least for our data - you may have transactions that this code doesn't recognize, but you can always modify it to match).

My solution was written natively for MS SQL Server 2008 or later, it uses a scalar UDF, a VIEW, and a windowed aggregate (SUM OVER (ORDER BY ...) which means it should be almost-syntax compatible with PostgreSQL. MySQL does not support OVER but you can perform a running-sum using a variable with arithmetic addition directly in the SELECT clause.

1. Create a database table with this schema (feel free to exclude columns you're not interested in, such as Option1Name):

   CREATE TABLE dbo.PayPalHistory (
       [RowId]                     bigint         NOT NULL IDENTITY(1,1) PRIMARY KEY,
       [DateTimeUtc]               datetime       NOT NULL,
       [Name]                      nvarchar(100)  NOT NULL,
       [Type]                      nvarchar( 50)  NOT NULL,
       [Status]                    nvarchar( 50)  NOT NULL,
       [Gross]                     money              NULL,
       [Fee]                       money              NULL,
       [Net]                       money              NULL,
       [FromEmail]                 nvarchar(255)  NOT NULL,
       [ToEmail]                   nvarchar(255)  NOT NULL,
       [TxnId]                     varchar(50)    NOT NULL,
       [CounterPartyStatus]        varchar(50)    NOT NULL,
       [AddressStatus]             varchar(50)    NOT NULL,
       [ItemTitle]                 nvarchar(255)  NOT NULL,
       [ItemId]                    nvarchar(50)   NOT NULL,
       [ShippingAndHandlingAmount] money              NULL,
       [InsuranceAmount]           money              NULL,
       [SalesTax]                  money              NULL,
       [Option1Name]               nvarchar(50)   NOT NULL,
       [Option1Value]              nvarchar(50)   NOT NULL,
       [Option2Name]               nvarchar(50)   NOT NULL,
       [Option2Value]              nvarchar(50)   NOT NULL,
       [AuctionSite]               nvarchar(50)   NOT NULL,
       [BuyerId]                   nvarchar(50)   NOT NULL,
       [ItemUrl]                   nvarchar(50)   NOT NULL,
       [ClosingDate]               nvarchar(50)   NOT NULL,
       [EscrowId]                  nvarchar(50)   NOT NULL,
       [InvoiceId]                 nvarchar(50)   NOT NULL,
       [ReferenceTxnId]            nvarchar(50)   NOT NULL,
       [InvoiceNumber]             nvarchar(50)   NOT NULL,
       [CustomNumber]              nvarchar(50)   NOT NULL,
       [ReceiptId]                 nvarchar(50)   NOT NULL,
       [Balance]                   money              NULL,
       [AddressLine1]              nvarchar(255)  NOT NULL,
       [AddressLine2]              nvarchar(50)   NOT NULL,
       [AddressTownCity]           nvarchar(50)   NOT NULL,
       [AddressState]              nvarchar(50)   NOT NULL,
       [AddressZip]                nvarchar(50)   NOT NULL,
       [AddressCountry]            nvarchar(50)   NOT NULL,
       [ContactPhoneNumber]        nvarchar(50)   NOT NULL
   )

   • Create a UNIQUE index on TxnId - you could use it as a primary-key, I suppose.

   • You might be tempted to create a Foreign-Key relationship between ReferenceTxnId and TxnId, however this will fail if you enforce it: we have many transactions where ReferenceTxnId points to a Transaction that doesn't exist. This is usually in the case of [Type] = 'Web Accept Payment Received' AND [Status] = 'Canceled'.

   • We also have some TransactionId values longer than 17 characters: some TransactionIds start with "U-" - all pending money requests, I suspect this indicates the transaction is "unfinished".

2. Re-download your entire PayPal History CSV files so that you have the latest retroactive updates.

   • Some PayPal accounts get the "full" history with all 41 columns, but some accounts only get a few columns in the downloadable CSV files. My Business Account gets all of the columns, but my Personal account doesn't. Check first before continuing.

3. Import these CSV files into this PayPalHistory table.

   • You will need to manipulate the data somewhat to import it, such as converting the separate Date and Time values into a single UTC DateTime value (check the Time Zone column in the CSV file).

4. Do a simple test to see how bad PayPal's default data is:

   1. Do SELECT TOP 1 [Balance] FROM PayPalHistory ORDER BY DateTimeUtc to get the official balance as-reported by PayPal in your imported CSV file. It should match the balance reported on your PayPal.com dashboard.
   2. Do SELECT SUM( [Net] ) FROM PayPalHistory to see the naive sum of all transactions that you imported - I'm assuming that you see a very different number compared to what you saw in the previous step.

   3. To find out where the differences are coming from, run this query:

      SELECT
          *,
          SUM( [Net] ) OVER ( ORDER BY DateTimeUtc, RowId ) AS RunningSum,
          NULLIF( SUM( [Net] ) OVER ( ORDER BY DateTimeUtc, RowId ) - [Balance], 0 ) AS RunningSumError
      FROM
          PayPalHistory
      ORDER BY
          DateTimeUtc,
          RowId
      

      (The ORDER BY (...), RowId is to ensure consistent ordering when multiple transactions share the same timestamp)

      As you scroll through the results, you'll see how the naive SUM is thrown-off from the official PayPal-computed Balance column.

5. So as you can see, the Net column value cannot always be trusted - what we need is to generate our own "EffectiveNet" value which is accurate - that is, the value is 0.00 for rows which do not affect the balance, instead of being what they are right now.

6. The problem is, given a single row of data (such as any single row from the example table in my original Question) we have no way of inherently knowing what its "EffectiveNet" is.

7. I have devised two functions to help solve this problem, the first function only looks at the ReferenceTxnId, Type and Status column values and generates accurate values for the vast majority of rows - indeed, in our dataset we only had one row for which this approach did not work. I recommend you try this one first and compare the running-sum value to ensure it works for your data:

    CREATE FUNCTION [dbo].[GetEffectiveNet] 
    (
        @net money,
        @type nvarchar(50),
        @status nvarchar(50),

        @refTxnId varchar(50)
    )
    RETURNS money
    AS
    BEGIN
        RETURN CASE @type
            WHEN 'Add Funds from a Bank Account' THEN

                -- When funds are added because of a send-money transaction (because funds are needed to meet the minimum balance required to send the money)
                -- then the balance-change takes-effect immediately.
                -- but when funds are added by-request, without a parent outgoing transaction, it waits until the 'Update to Add Funds from a Bank Account' txn for the balance-change.
                CASE @refTxnId
                    WHEN '' THEN NULL
                    ELSE         @net
                END

            WHEN 'Update to eCheck Received' THEN 

                CASE @status
                    WHEN 'Canceled' THEN NULL
                    ELSE                 @net
                END

            WHEN 'Web Accept Payment Received' THEN

                CASE @status
                    WHEN 'Canceled' THEN NULL
                    WHEN 'Cleared'  THEN NULL
                    ELSE                 @net
                END

            WHEN 'Cancelled Fee' THEN

                CASE @status
                    WHEN 'Completed' THEN NULL
                    ELSE                  @net
                END

            WHEN 'Update to eCheck Sent' THEN
                NULL

            WHEN 'Donation Received' THEN

                CASE @status
                    WHEN 'Cleared'  THEN NULL -- Weirdly, "Donation Received"/"Cleared" only take effects after the follow-up "Update to eCheck Received", but "Web Accept Payment Received"/"Cleared" takes effect immediately. I don't know why PayPal seems to place a kind-of hold on Donations but not WebAccept, weird.
                    WHEN 'Canceled' THEN NULL
                    ELSE            @net
                END

            WHEN 'Authorization' THEN

                CASE @status
                    WHEN 'Completed' THEN NULL
                    ELSE             @net
                END

            WHEN 'Request Received' THEN

                CASE @status
                    WHEN 'Pending'  THEN NULL
                    WHEN 'Canceled' THEN NULL
                    ELSE            @net
                END

            ELSE
                @net
        END

    END

You can use this function in the query like so below, hopefully it should give you an accurate running-sum and balance figure at the end:

<pre>
SELECT
    *,
    SUM( dbo.GetEffectiveNet( [Net], [Type], [Status], [ReferenceTxnId] ) ) OVER ( ORDER BY DateTimeUtc, RowId ) AS RunningSum,
    NULLIF( SUM( dbo.GetEffectiveNet( [Net], [Type], [Status], [ReferenceTxnId] ) ) OVER ( ORDER BY DateTimeUtc, RowId ) - [Balance], 0 ) AS RunningSumError
FROM
    PayPalHistory
ORDER BY
    DateTimeUtc,
    RowId
</pre>

8.

• In our data, we had a few cases where the Type, Status, and lack-of ReferenceTxnId information was not enough to determine the "EffectiveNet" value - specifically the transaction was a "Recurring Payment Received" - which should have had an EffectiveNet of 0.00 however there was nothing to distinguish it from other non-zero Recurring Payment Received payments.
• I wrote a recursive CTE (Common Table Expression) query to retrieve all descendant rows from a given TxnId (it recursively followed all ReferenceTxnId values) and I saw that this particular row had a single child with [Type] = 'Update to eCheck Received' AND [Status] = 'Updated', and it was on this follow-up row that the balance was changed.
• I wrote a more robust function that computes the EffectiveNet in a way that also takes into consideration child rows. Fortunately this was the only case I needed to handle that requires specifically inspecting child transactions - all other transactions can be handled very similarly to before.
• So here is the rewritten function (it does run slower compared to the previous function) which is more accurate

    CREATE FUNCTION [dbo].[GetEffectiveNet_Improved] 
    (
        @net money,
        @type nvarchar(50),
        @status nvarchar(50),

        @txnId varchar(50),
        @refTxnId varchar(50),

        @relatedCount int

    )
    RETURNS money
    AS
    BEGIN

    RETURN CASE @refTxnId
        WHEN '' THEN -- no referenced parent, this is a root transaction

            CASE @relatedCount

                WHEN 0 THEN
                    CASE @status
                        WHEN 'Pending'  THEN NULL
                        WHEN 'Canceled' THEN NULL
                        ELSE                 @net
                    END

                ELSE
                    -- @relatedCount > 0, Money Sent

                    CASE WHEN @net = 0, Money Received

                        CASE @type
                            WHEN 'Add Funds from a Bank Account' THEN

                                CASE @status
                                    WHEN 'Completed' THEN NULL
                                    ELSE                 @net
                                END

                            ELSE

                                CASE @status
                                    WHEN 'Cleared'  THEN NULL
                                    WHEN 'Canceled' THEN NULL
                                    ELSE 

                                    -- Need to manually check the tree at this point.
                                    -- Our example is (type: "Recurring Payment Received", status: "Completed", net: +18.95)
                                    -- There is nothing in the row to suggest the payment should not be considered immediately (i.e. it doesn't say it's an eCheck), however there WAS/IS a follow-up transaction that's (type: "Update to eCheck Received", status: "Updated") and this is the TXN that adjusts the balance.
                                    -- So to handle this case, we need to inspect the related transactions and return NULL if the follow-up is 'Updated'

                                    -- I need more data to verify this assumption works, until then I have to special-case it:

                                        CASE
                                            WHEN @relatedCount = 1 AND ( SELECT [Status] FROM PayPalHistory WHERE ReferenceTxnId = @txnId ) = 'Updated'
                                                THEN NULL
                                            ELSE
                                                @net
                                        END -- CASE WHEN @relatedCount = 1 AND ...
                                END -- CASE @status
                        END -- CASE @type
                    END -- CASE WHEN @net  '', a child row

            CASE WHEN NOT EXISTS( SELECT 1 FROM PayPalHistory WHERE TxnId = @refTxnId ) THEN

                CASE @type
                    WHEN 'Cancelled Fee' THEN

                        CASE @status
                            WHEN 'Completed' THEN NULL
                            ELSE                  @net
                        END

                    ELSE @net
                END

            ELSE

                CASE @type
                    WHEN 'Update to eCheck Received' THEN

                        CASE @status
                            WHEN 'Cleared'  THEN @net
                            WHEN 'Canceled' THEN NULL
                            WHEN 'Updated'  THEN @net
                            ELSE                 @net
                        END

                    WHEN 'Update to eCheck Sent' THEN

                        CASE @status
                            WHEN 'Cleared'  THEN NULL -- When sending money, net is deducted from balance immediately after sending, before it clears; whereas when receceiving it's added only when it clears, not when it first arrives.
                            ELSE                 @net
                        END

                    WHEN 'Add Funds from a Bank Account' THEN

                        CASE @status
                            WHEN 'Completed'  THEN @net
                            ELSE                   @net
                        END

                    ELSE @net
                END

            END

    END

    END

9. And here's the view that ties it all together:

   CREATE VIEW PayPalHistoryWithAccurateBalance AS
   
   SELECT
       *
   FROM
   (
       SELECT
           *,
           SUM( EffectiveNet ) OVER ( ORDER BY DateTimeUtc, RowId ) AS RunningSum,
       FROM
       (
           SELECT
               *,
               dbo.GetEffectiveNet_Improved(
                   [Net],
                   [Type],
                   [Status],
                   [TxnId],
                   [ReferenceTxnId],
                   ( SELECT COUNT(*) FROM PayPalHistory AS [related] WHERE [related].ReferenceTxnId = PayPalHistory.TxnId )
               ) AS EffectiveNet
           FROM
               PayPalHistory
       ) AS [inner]
   ) AS [outer]
   

10. Used like so:

    SELECT
        *
    FROM
        dbo.PayPalHistoryWithAccurateBalance
    ORDER BY
        DateTimeUtc,
        RowId
    

I hope this helps anyone else wanting to do accurate bookkeeping with PayPal Transaction History files!