Trouble constructing accounts from PayPal CSV import

Hi. I could really do with some advice as I’m up against it time wise to get my first VAT return in.

My book keeping is mediocre so its possible I’m complicating things! Whilst migrating to quickfile from my existing software I hit the VAT threshold without realising. After sorting out VAT registration with HMRC I have to submit a return from 1st of October to end of this month April so my VAT return spans across two years and two systems!. I only have March 2020 - Now in QF so I’ve decided to attempt to create accounts in QF back to 1st of October last year so I have the full VAT period that need on the return that will be submitted by QF via MTD shenanigans.

The first thing I did was bring in my bank statement and this went well and it all reconciles nicely.

Now for PayPal. This is a horrible experience as I have about 4k invoices to bring in. I started with just a months worth but there are numerous problems. Firstly QF is adding an extra penny here or there, secondly there’s no way to import fees and thinking of manually adding them up and lumping them daily for the past 5 months made me cry.

I’ll cut to the chase, can I just input sales and fees from PayPal as lumps on single invoices at the end of each month and just import the purchase invoices separately? In my mind this would be sufficient in case of an enquiry as my purchases are there and I have PayPal statements showing individual sales if required this would be a hell of a lot less painful but is it good enough?

Please please advise if you can, thanks.

Hi @karlj

Given you have the record in another system, I believe this is acceptable. However, it’s definitely worth consulting with an accountant just to be sure everything is accounted for corrected and in a suitable way for HMRC. Strictly speaking, PayPal is your true record for these sales anyway.

But, if you did want to import the PayPal transactions, I’ll try and address those issues for you.

This shouldn’t be happening. Providing the CSV from PayPal is a 2 decimal number, then this is what should be imported. If you do have any cases where this is happening, please don’t hesitate to send me a private message with some details and we’ll investigate this.

There is, and there isn’t.

It is possible to do it from the CSV file by tweaking the file itself and using manual mapping. I don’t have an example at hand to give you some step-by-step instructions, but as above, if you did want to send me your CSV file in a private message (click my name, and then “Message”), I’ll try my best to get things going in the right direction for you.

Thanks for taking the time to answer. I think initially I’ll consolidate into single entries just to get the balance sheet right for the VAT return and take a bit of pressure off. Next I’ll look at getting the transactions in from a CSV again, I did notice that the format of the gross column in the CSV was set to general and not currency by default perhaps this is why the extra penny was getting added on. I’ll correct this and experiment before looking for help, I appreciate the offer of support. I will need information on manual mapping fees at some point so I will likely drop you an email, thanks again for your help.

A CSV file has no formatting. It’s just a text file. Viewing it in Excel is just for convenience, but none of the styles or formatting are applied to the output file. If you open it in Notepad you will see the actual figures used, not what Excel might have decided to show you.

1 Like

I get what your saying. I did a test where QF added the penny then opened in notepad and the price is correct so this appears to not be a format problem, thanks.

Could well be a rounding issue further up the calculation tree at the PayPal end. They might be using 4DP internally and then rounding it to 2DP (somehow differently to the previous rounding) for the export.

Im not sure what that means but this is the contents of the csv copied from notepad just three invoices. The ones ending .99 get rounded up.

Date,Currency,Gross,Fee,Net,Transaction ID,Name
01/10/2019,GBP,9.99,-0.59,9.40,6E390640VD067354W,Ana Paola Lima Perea
01/10/2019,GBP,6.49,-0.49,6.00,65654615DR1390347,Angela Boyle
01/10/2019,GBP,6.99,-0.50,6.49,8XV23622L4281170F,alison padmore

I still can’t get a clear picture as to what is being rounded up. The gross, the fee or the net?

If its the fee, I wouldn’t worry to much as its in your favour and hmrc are happy with rounding differences. If its the gross then that’s another issue as it would obviously wouldn’t be in your favour.

Quickfile will only import the gross.

Fair enough. So in that case what happens if you strip the csv file of the fee and net column and just use the gross?

I think I’ve further isolated the bug. It’s nothing to do with paypal it’s Quickfile. When creating invoices from CSV QF rounds up .99 by a penny e.g £6.99 becomes £7.00.

I’ve never had that issue before and I regularly import sales invoices via csv import.

test.csv (102 Bytes)

CSV item is £6.99 honestly!

Bizarre. I’m intrigued when things like this happen, often ends up going to PM and then we don’t get a conclusion!

It does it using the CSV from backups also, this is very worrying.

I’ve just done a test myself. I uploaded a csv with an invoice for 6.99 and it was added correctly with no rounding up.

Could you provide a screen shot of your csv file to check how’s its formatted and the headers you are using?

Check the posts above, there’s extracts and sample files attached.

This is what I do. I create a csv with the columns date, description, invoice number and Gross amount. It works fine. So it must be your PayPal csv. If you copy the columns with the data you need in to a new csv with the column headers I’ve listed above. It should work.

I get the same result when I use the invoices CSV from a QF back up.