search

nordbergm / stripe2ofx

A small command line utility that converts the Stripe transfer exports (CSV) to the OFX file format often used by accounting software for bank statement imports.
2 stars 2 forks source link

workflow in accounting software #6

Open binary1230 opened 9 years ago

binary1230 commented 9 years ago

My accountant ideally wants to see the following things separated out once things have been imported into the accounting software:

1) Stripe gross sales 2) Stripe refunded sales 3) Stripe fees (both from refunds and gross)

My strategy in Xero (and it should work with anything) is:

Part 1:

All Stripe bank account transactions are net sales. Net sales = gross sales - fees

For the date range wanted, create transfers of each net sale into a "Stripe Clearing Account" which is a current asset, or a bank account.

Now, the "Stripe Clearing Account" contains the combined net sales for the given time period.

Part 2:

Then create either a journal entry or an invoice/bill containing the following:

Deduct from Stripe Clearing Account: total net sales for that time period

Put into other accounts the following amounts, gotten from the columns of the transfer CSV file (which should add up to be net sales) sum of: charge gross sum of: charge fees sum of: refund gross sum of: refund fees sum of: adjustment gross sum of: adjustment fees

PLEASE NOTE: Occasionally the "net sales" and these other columns can come out to be negative, so if an accounting software doesn't allow negative totals in invoices, you have to use a bill instead. Using just journal entries may solve this issue. This is something we need to take into account when writing the code.

what the code should do

Ideally it should setup as many of these transactions for Part 1 and Part 2 as possible so the only thing the user needs to do is manually reconcile the bank statement with our transactions we've created.

Disclaimer: This is not necessarily all true, just, was my experience doing it manually with real data from Stripe and Xero this weekend : )

nordbergm commented 9 years ago

Is there a reason why we would have to move the detailed transactions from the clearing account to the transfer account? As I see it that would require two statements to be generated and the statement for your transfer account wouldn't match your real bank account statement (because it only has a lump sum).

Part 1 In our case we've chosen to produce charges, fees, refunds and refunded fees as transactions in the clearing account and then a lump sum transfer at the end. This way you only need a single OFX statement produced and imported, the lump sum transfer would be reconciled with your actual bank statement import or Xero bank feed. It also supports situations where the transfer account is of a different currency (ours is GBP but clearing is USD).

Part 2 If we left it as it is for the clearing account we don't have to produce any transactions in the transfer bank account since they have already been reconciled in the clearing account. Lump sum has worked fine for us and our accountant is happy (but we're in the UK so might be different regulations).

Edit: Oh, and we create invoices against the clearing account and the transaction holding the gross sale, the fee transactions are reconciled as Bank Fees in Xero.