Forum Discussion

ELC-Leo's avatar
ELC-Leo
Experienced Cover User
2 years ago

Rounding Issues with Importing Sales

Can someone please tell me how to avoid MYOB AccountRight rounding line items incorrectly when importing sales data, in my case Service Sales, but judging from the forums, the issue occurs in other types of sale. I have exhausted the Community Forum. And the few users I found with similar rounding issues have had their threads closed without resolution.

 

After spending a day on developing a process for importing service sales from a CSV to MYOB AccountRight, I really hope you won't tell me this is not possible. Or am I wasting yet more time trying to lay this all out in writing in the forum…

 

To set up the import process, I was working off MYOB's information here: https://help.myob.com/wiki/display/ar/Import+and+export+fields#expand-Servicesales\

and https://help.myob.com/wiki/display/ar/Importing+data , as well as Moderator and user advice in the forum. But having got everything working on my side, I now come up against MYOB adding rounding errors. I believe this rounding issue is a known error, and if so, it should have been mentioned in the initial information so that I could have saved myself the time and effort.


The issue is that MYOB amounts are, for line items of certain values, increased or decreased by $0.01.

The best way to explain is to see the issue, so I will attached pictures of my import data and the resulting invoice in MYOB. If possible, I will also attached the .txt file with the CSV data.

 

Additionally, during some tests the Total Amount field was also one cent off what it should be, such that the amounts in the Amount column did not sum to give the Total Amount!

 

Thank you for any solution.

7 Replies

Sort By
Replies have been turned off for this discussion
  • gavin12345's avatar
    gavin12345
    Ultimate User
    2 years ago

    Hi ELC-Leo 

     

    I'm not sure about your Inc Tax Amount column. Inc usually stands for Inclusive, so I think this should show 1.00 2.00 etc.

     

    Most rounding issues are with GST inclusive numbers like $5.00. Divide this by 11 to get 0.45454545 and MYOB struggles.

     

    This is what I suggest to see if MYOB can import correctly.

     

    Fix up this invoice onscreen to what you want the amount column to show. Next export this invoice only. Then import the export, without changing anything. If it succeeds, analyse the export file and copy that to the letter. If it fails and gives you the same result as the original, then there is no answer but to use something else.

     

    Regards

    Gavin

    • ELC-Leo's avatar
      ELC-Leo
      Experienced Cover User
      2 years ago

      Good suggestions, thank you, Gavin. 

      I have tried various combinations of tax-inclusive/exclusive figures. But I will try your suggestion of fixing it in MYOB, exporting and reimporting to get a handle on the situation. 

       

      Kind regards

  • AlanT's avatar
    AlanT
    Partner
    2 years ago

    Hi ELC-Leo 

     

    I notice in your screen shots that you are displaying your CSV from MS Excel.

     

    Just out of interest, are you able to open the CSV file in Notepad and show the raw import data?

     

    As an Excel programmer, I have had this type of problem before where Excel tries to be too smart and modifies the data so it can display it the way it wants to, but leaves the underlying data as it is.  Therefore, sometimes what you see in Excel looks good, but XL is tricking you.

     

    Regards,

     

    AlanT

    • ELC-Leo's avatar
      ELC-Leo
      Experienced Cover User
      2 years ago

      Hi AlanT,

      Good suggestion for many, but in this case, I don't think that is the cause.

      Here is a screenshot of the CSV data in Notepad (the .txt file is attached to original post).

      Kind regards

       

      • Mike_James's avatar
        Mike_James
        Ultimate Partner
        2 years ago

        Hi ELC-Leo , when importing sales, AccountRight takes the net amount and calculates its own GST for the line. However, it also accumulates the net and GST on every line, and adjusts the GST as it goes to ensure that the cumulative total net plus GST (calculated on that total) gives the total inclusive.

         

        It is impossible to replicate this process outside MYOB, or to provide figures which will give the wanted inclusive line amounts, since only the net amount is used during the import. 

         

        I've prepared an analysis showing how the inclusive figures on your invoice are arrived at. 

         

        Note that the GST-inclusive setting is only for display purposes.