Import wizard tool for multicurrency

Support
1

Hi,

I’m not entirely sure if this is a bug or if I am doing something wrong so any advice would be greatly appreciated

The majority of our business is conducted via UK bank accounts with a currency of GBP but we also buy & sell some products in USD via a paypal account with a USD balance
In Quickbooks I have set up a Paypal Merchant Account with an account currency of USD to represent this account (nominal code 1258) and I’m currently trying to import our USD purchase history
It all seems to works fine if we add the purchases manually however the results using the import wizard are strange and as we have 000’s of transactions we cant add them all manually

e.g. We purchase some parcel tape for $10 and pay using the USD paypal account
This should be recorded as a $10 packaging expense (nominal account code 5003) and a $10 decrease of the USD paypal account (nominal code 1258)

Import wizard: In the purchase invoices import wizard I upload a csv with the following data
Gross value: 10
Purchase nominal code: 5003
Paid nominal account: 1258

The import wizard then creates the following:
-A new purchase receipt (QF00791) with a value of £10, attributable to the packaging account and marked as ‘paid in full’ by a payment (PP000799) of £10
-A new transaction in my USD paypal account of $10 money out tagged as payment for the new purchase receipt (‘This payment […] has been allocated to invoice #QF00791’)

£10 does not equal $10 and so as a result my expenses look to be higher than they really are (appear as £10, not $10)

I can provide screen shots of the PO, PP and bank statements if necessary - any advice you could give would be great!

Many thanks,

Katie

[Note: Manually it works fine
I go to the USD Paypal account screen, click ‘add a transaction’ and create a transaction with $10 money out
I then can tag this as ‘payment to a supplier’ and have the option to 'Create a new purchase record for this transaction’
I add the supplier details, select ‘packaging’ as the category and click save. This creates a new invoice with a value of $10 and attributes it to ‘packaging’ - just as I expect]

2

What is your year end? how much transactions in $ you have since year end?

3

We’re a new company and only started in July so haven’t input a year end yet
Since we started we’ve probably had about 2000 transactions fromt eh USD account I think

4

You can try another thing to make life easy, create tag rules based on $ paypal account, e.g Parcel tape in description should end us in code packaging expense. Turn on auto tagging, once done upload paypal feed to $ account and it will automatically pick all transaction with description Parcel Tape and allocate to packaging expense. Make sure to delete any existing csv import or paypal feed you have done

5

The import wizard currently only supports GBP invoices, everything will be treated as GBP. We only recently launched the invoice import tool so it may get updated in due course to support foreign currency transactions. In general foreign currency imports will be more complex as you need to handle exchange rates.

Also PayPal presents it’s own set of problems as you can potentially have GBP and other foreign currency transactions in the same bank account, you’d probably need to normalise these to GBP, I would have a word with your accountant for clarification.

6

Thanks for your answers

I understand that the import tool may only be designed to work with one currency and that’s fine - I suppose we’ll just have to go through and add the transactions and supplier details manually

The reason I flagged it as a bug is because importing transactions to an account that is in a secondary currency doesn’t seem to be working properly - the expense and the transaction need to balance so it should never be creating a transaction of $10 and marking this as a full payment for a £10 expense

When we first uploaded the USD data we were a bit shocked as our expenses were significantly higher than expected but, after finding the problem with the import tool, we realised they were being over stated by c.33% as the expense values were the USD values but they were being labelled as GDP
However other users might not be aware that this is happening and so their total expense costs and profits will all be wrong so I just wanted to make sure you guys were aware of it

Otherwise I think QuickFile is a fantastic problem - it’s great!

7

Perhaps you can export all purchase data from paypal to csv, insert another column called GBP and convert all us dollars in to that, same for payments and import them for all existing 2000 transactions, new ones you can handle as you go

8

We will look at putting a warning on the import tool. Although in the CSV upload there is no field for currency so it will automatically be assumed as local currency. Just wondering why you assumed it would upload as USD, did you prefix the amount with a $ symbol? Sorry, just trying to understand how people use the software so we can improve our guidance.

9

Hi Glenn,

Sorry about this - I don’t think I’m explaining it too well - I have recreated my problem using just one transaction as an example and included screenshots so hopefully this explains it more (however I am only allowed to upload 1 picture to the post so screen shots are all at the bottom:
(Note: My primary account is in GDP)

A. I enabled multicurrency and created a PayPal account with USD currency - this has a nominal code of 1258:

B. I wanted to create a csv to import transactions but couldn’t see any explanation about how to import multicurrency purchase orders and (mistakenly) assumed the transaction currency my be set by the currency of the paid account. So I uploaded my USD purchases with the paid account set as 1258 and thought this would result in the purchases being imported in USDs

I originally did this with all my USD transactions but I think screen shots of this would be more confusing so as illustration have replicated the process with the example of a purchase of parcel tape for 10USD (equating to c.6GBP)

C. I imported the file using the Purchase import wizard and got confirmation that the upload was successful

D. I checked to see if it had imported correctly by looking to see if the purchase has appeared in the PayPal USD bank account and, as the import had created transactions in USDs, and consequently my balance reconciled with my USD bank statement, I assumed everything was OK

E.g. For the parcel tape example the import wizard has imports a transaction with a value of $10 and, consequently, the balance of the bank account on the summary screen is showing as -$10

E. However, when I looked at my expenses they were higher than anticipated
To find out why I checked the transactions page of the relevant nominal accounts and saw that all the purchases that I assumed had been assumed as USD values were acutally appearing as GBP values

E.g. The packaging account (5003) shows the import wizard has imported the purchase as 10GBP, not 10USD / 6GBP:

F. It appears that the import wizard has created transactions and expenses of the same magnitude but in different currencies and then matched them and marked them as ‘paid in full’ - which doesn’t seem right

E.g. 10GDP expense marked as paid by 10USD bank transaction

I hope this makes more sense - however please let me know if you need me to elaborate further on it and I’ll do my best!

Many thanks,

Katie

1375×2450 214 KB

10

Thank you for the screenshots, it really does make my life much easier :smile:

I can see now that this is definitely a bug, basically the import process only being able to create invoices in GBP should not then allow payments from a bank account in USD (or any other foreign currency). We need to be checking the nominal 1253 to ensure that it’s actually a GBP account before committing the data.

Of course the long term objective would be to allow for the invoice currency and payment currency to be specified. This however is more complex than on one would assume and will require quite a lot of refactoring. For now we will commit to adding the check to validate the bank account currency.

Thank you for providing a full explanation and I’m sorry we couldn’t do more to automate this.

11

No worries Glenn - thanks so much for your speedy response - brilliant customer service!

1 Like