I’ve uploaded all Stripe transactions and all Natwest transactions.
Unfortunately, tagging transfers from Stripe to Natwest has had the unintended consequence of creating duplicate transactions in Natwest.
Although I have reversed this through a bunch of manual deleting, can someone explain what I did wrong and how I can prevent this from happening again?
A common issue with the transfers is the difference in dates. For example, Stripe may deposit funds on a Thursday but they may not hit your account until the Friday. As a result, when it comes to tagging them, the matching transaction wouldn’t be found.
Where did the original transactions on the bank come from? For example, were they from a feed or import?
You should only ever tag transfers from one end. So I’d recommend excluding the transfers from the stripe import and instead tag them from the NatWest account, which will create the matching outgoing transactions on the stripe side. If you’re using the stripe auto feed I believe there’s an option to ignore payout transactions.
Ideally you’d have it ignore payouts and fees (for which you get a once a month invoice from stripe anyway, which needs to be reverse charged for VAT), but I’m not sure that’s an available option.
I’m not using the native Stripe integration and I don’t have the choice to ignore payout transactions. I also get transaction lines for every fee (not the end of the month). To put things into context, we already developed a system that creates bank statements from the Stripe API (happy to share if anyone wants).
Stripe aside, these are 2 bank accounts and each one has its own list of debits and credits. When it comes to transfers, what I should be doing is mapping the debits of one to the credits of the other (not creating debits/credits in the other). Isn’t that possible?
Regarding the fees, there are no ways to ignore these at the moment, although you’re welcome to start a Feature Request request for this if you wish and we can take a look at adding it.
It is possible if the dates and amounts match. If these match, it should prompt you to select the pre-existing transaction rather than creating a new one.
The corollary being that if the dates don’t match but you want both ends to maintain the (different) dates from their feeds then you’ll need another bridging bank account to hold the “money in transit” between the time it leaves one account and the time it arrives in the other, and tag both ends separately as transfers to/from the bridging account.
@QFMathew Unfortunately, your solution doesn’t work for me. As mentioned previously, I am not using QuickFile’s native Stripe integration because it is lacking. So, instead, I’m using a 3rd party solution that converts Stripe data into traditional bank statements. For example…
So, for all intents and purposes, we have to consider Stripe as being just another bank account.
Whilst I appreciate @ian_roberts hack, it seems the real solution is for QuickFile to be a bit more relaxed on the time factor when matching transfers to account for transfers which might take a bit longer than the same day to be transferred.
Unfortunately we wouldn’t be able to relax the rules on the dates as this would cause discrepancies. With double-entry bookkeeping, if the dates were different, it could cause a problem with the balances.
So they presumably use the same trick under the covers somewhere of a balance sheet nominal code to hold the money while it’s in transit and keep everything else in balance (if I ask for a balance sheet on the day in between then the money has to be somewhere, it can’t just vanish and re-appear the following day).
There would need to be some intermediate account as you can’t have the double entry disappearing while the transfer completes. From what I know Xero will allow you to link two entries form different accounts on different dates but they just alter the dates to align. That would seem the case from reading here.
From a usability point of view, however they do it is better than all the options offered so far.
From an accuracy point of view, having QF automatically create a “Bank1Bank2Transit” ledger would be genius and I’ll tell you why…
As a new user (coming from Xero, QBO and Wave) who is setting up the accounts for several companies with a backlog of transactions, I have repeatedly made the same mistake of tagging transfers on Bank 1 without having uploaded the statements of Bank 2 thereby causing lots of transactions to be created in Bank 2 in addition to the not-yet-uploaded statements. (Another flavour of the same problem I’m experiencing with Stripe.)
What would have made more sense from a usability point of view would be for QF to automatically create BankXBankYTransit ledgers the first time a user tags a transfer between BankX and Bank. Then, “matching” transactions would have the effect of creating 2 sets of debit/credit entries between the 2 banks and their transit account, whilst tagging a transfer without a corresponding transaction would create 1 set of debit/credit entries in the transit account and mapping it to an already tagged transaction would also create 1 set.
So, rather than have lots of duplicate entries created inadvertently, the transit ledgers could be highlighted in the UI when they don’t balance. (eg “You have £300 in transit between Bank X and Bank Y”).
Even if I have to do the above manually as per @ian_roberts advice, perhaps QF can offer another category of bank accounts called “Transit Accounts” so that they can all be listed out of sight at the bottom.
It’s a solution I suppose but another layer of complexity. Some users have 50+ virtual bank accounts and if transit accounts were created for many different permutation of movements between those accounts it will quickly create a mess in the chart of accounts, TB and balance sheet.
I kind of see the use of having one transit account for all inter-bank movements, but then you still would need some background scheduling tasks to manage future date transfers. If anything went wrong with the reconciliation there and you have 100s of credits and debits, it could easily become time consuming to isolate.
These settlement delays are not an uncommon problem in accounting, but the date on the bank account should take priority. With PayPal for example there’s an option to just filter out withdrawals from the feed so that when you import your bank statement you just tag from the bank to the PayPal account.
I tried to create a new account called NatwestStripeTransit and tag transactions in the Natwest and Stripe accounts to/from this transit account.
I imagined that all the transfers would balance each other out to leave a balance of 0.
But I end up with a massive discrepancy of about £2,000 and no way of figuring out how/where it/they came from as there is no way for me to match individual items against each other.
Note: Nothing is in transit because the Natwest account was closed 2 months ago, so these are all old transactions.
What do you suggest? Not balancing and not knowing how to debug the imbalance doesn’t sit well at all.
How many transactions do you have going in/out of that holdings account? You’d normally iterate forward from a given date until the balance deviates by a consistent amount (in your case £2,000).
Stripe pays out daily, so there were about 900 I had to go through. Unfortunately, there is no way to export all the transactions to track down the culprits, so I had to go from page to page, copying/pasting 50 results and then deleting all of the matching ones to hunt them down.
Although that kept me awake very late last night, I did eventually find them. In a nutshell, there were about 5 transactions which had been tagged incorrectly and another 8 payouts from Stripe that never made it to the bank account (which I am now taking up with Stripe).
Now for some really good news: Whilst doing the above, I discovered that, when Stripe does a payout, it gives 2 separate dates: Created and Arrival. I’m not sure if they initiate the payout transfer on the created date and then estimate it to arrive on the arrival date, but from my sample size, the arrival date is very accurate.
With that in mind, we’re tweaking the Stripe statement app to work with arrival dates (rather than create dates) in hope that we can map transfers directly without using the transit accounts.