locked
How can I get Money to export amounts converted to the base currency to excel? RRS feed

  • Question

  • Hi

    I have some transactions in a foreign currency which Money automatically converts to the base currency for reporting purposes, however, when exporting to CSV, Money seems to just drop the currency symbol but does not do any conversion.

    Is there a way for for Money to export the converted base currency amount instead.

    Thanks

    Friday, September 7, 2012 6:32 AM

Answers

  • View your report. Right-click into your report. Paste into a spreadsheet. Expect what is pasted to match what the report looked like. Save as a CSV if that is what you want.

    For regular transactions on the Account transactions report, the individual amounts for transfers will have the currency symbols. The totals and running balance will be converted into the base currency, but not the individual transactions. If I use
    Customize->Rows&Columns->SubtotalBy:None, transfer amounts also show the unconverted amount with the currency symbol..

    For transfers I find that , if I use
    Customize->Rows&Columns->SubtotalBy:Categories,  the individual amounts for transfers will be converted into the base currency.
     
    Alternatively , right-click into your report, and choose Export. There will be a filename dialog, but it comes out as a *.txt file. That file will have the currency symbol with the number as it does when viewing the report in Money.

    Friday, September 7, 2012 5:29 PM
    Moderator

All replies

  • View your report. Right-click into your report. Paste into a spreadsheet. Expect what is pasted to match what the report looked like. Save as a CSV if that is what you want.

    For regular transactions on the Account transactions report, the individual amounts for transfers will have the currency symbols. The totals and running balance will be converted into the base currency, but not the individual transactions. If I use
    Customize->Rows&Columns->SubtotalBy:None, transfer amounts also show the unconverted amount with the currency symbol..

    For transfers I find that , if I use
    Customize->Rows&Columns->SubtotalBy:Categories,  the individual amounts for transfers will be converted into the base currency.
     
    Alternatively , right-click into your report, and choose Export. There will be a filename dialog, but it comes out as a *.txt file. That file will have the currency symbol with the number as it does when viewing the report in Money.

    Friday, September 7, 2012 5:29 PM
    Moderator
  • Hi,

    Interesting to see how excel handles the currency symbol if you use the cut and paste option, it just errors with #NAME? as it is trying to do something clever with the symbol and trying to interpret it as a function, other than a manual fix I can't think of away round this.

    Also, the only way I can think to find the base currency value for a manual correction in Excel is to edit the transaction in Money and press F8, is there a simpler way?

    I'm trying to streamline an export and import into YNAB process for budgeting purposes, I have it working if I export at the category level as Money is only using the base currency but as soon as I try the same down at the sub-category and export all the transactions I run into this currency problem. Its not a huge problem as it is often only once or twice a year and once I've moved on from that month the data does not change so I do not need to keep exporting anyway.

    Thank you for your support.

    Ian

    Saturday, September 8, 2012 6:00 AM
  • Normally Excel is very forgiving with data formats.

    Whether I copy & paste or export to CSV, any cells with the £ (british pound sign) are set to a format of currency. Other cells with just numbers have their format set to number. Other cells containing the non-base currency symbol of Excel

    would normally be set to general.

    What exactly is the data in the cell showing the #NAME? error ?



    • Edited by AshleyST Saturday, September 8, 2012 10:38 AM
    Saturday, September 8, 2012 10:29 AM
  • I have a couple ideas. If you are working with Excel, consider the running balance. Some lines will show blank, and the others will have an actual running balance. Suppose you add a column (I will arbitrarily call it column K, you could use an IF that says if the running  balance to the left is not blank, use that balance in column K. If it is blank, use a copy of the previous row of column K.

    Now column L could be the difference between the column K value and the previous row of column K. That difference will give the foreign currency as converted to the base currency. You could get fancier still, and only show that difference if the running balance is non-blank on that row; else show a blank.

    If you got fancier, I think you could use VBA in Excel to figure out the currency and do who-knows-what. I don't use VBA.

    I hope AshleyST can help you with the Excel error. I don't get an Excel error when I paste my test report into Excel 2007. If I did, I would right-click and choose one of the PasteSpecial options. You could also try pasting into a different spreadsheet program...

    Saturday, September 8, 2012 8:42 PM
    Moderator
  • Hi

    The cells that show the #NAME? error contain data formatted like

    =-kr7.00

    It is only the few transactions in Norwegian Krone that have a currency symbol as Money does not show the symbol for the base currency, I'm not sure whether it is actually a currency problem or just the fact I am pasting non-numeric data.

    Ian

    Sunday, September 9, 2012 5:18 AM
  • Hi

    The running balance idea works well, if I add a new running balance column, then, as you say, simply working out the difference between the new running balance and the previous one it gives me the transaction value.  I can then simply paste the new values over the imported ones.  There is no need to use any IF or fancy currency calculations :-)

    I have noticed some very strange excel behaviour though, to help me spot which cells had new values I added the following formula in a new column.

    =IF(F8=H8,"OK","Not OK")

    Rather unexpectedly excel reported many of the rows as 'Not OK' when clearly they were, or appeared to be.

    After much head scratching I realised that the values were not all they appeared to be for example

    -74.99 - -74.50 was giving a result of -0.48999999999995 and not -0.49

    -85.56 - -75.99 was giving a result of -9.57000000000001 and not -9.54

    Using Round to 2 decimal places seems to do the trick, but I now need to find out why I had the problem and whether there is a better solution, the problem does not seem to be in the imported data but in the result of the calculation, but it would probably be better for all the Money people on this forum, if I take the debate to an Excel forum.

    Sunday, September 9, 2012 6:23 AM
  • Yes, I can recreate that error with an account containing the Norwegian Krona symbol. In fact, you can do it yourself by

    just typing in a minus sign followed by a couple of characters and then numerics ( for example -op45).

    It's excel trying to be helpful for you but of course, in this instance it's not what you want. I assume the cells with the kr value

    are always in the same column ? If so, the easiest way to avoid the problem is to set that column format to text.

    In my test, I had column E set to text and pasted in my transaction report and had -kr59.45 in the cell. I was

    subsequently able to do arithmetic on it once I had dropped the -kr in any formulas referencing the cell.

    • Edited by AshleyST Sunday, September 9, 2012 4:04 PM
    Sunday, September 9, 2012 3:57 PM
  • Hi,

    Converting to text is certainly one option, but as I've only ever been to Norway once and cannot foresee when I might go again the better option for my problem is that proposed by Cal and that is to simply recalculate the value for every transaction irrespective of which currency it is in, this way it will always be right regardless of the currency.  I just need to write the VBA.

    I do appreciate your help, so perhaps you could suggest an explanation to my unexpected results problem I posted further back.

    Regards

    Monday, September 10, 2012 6:14 PM
  • Here is an example of an experiment in Excel without VBA: When I pasted the Account Transactions report with the Category  column selected, but not Memo, Account, or Cleared, the Opening Balance value was in F9 after pasting into A1.

    Into G9 I entered
    =IF(ISNUMBER(F9),F9,G8)
    and into H9 I entered
    =IF(ISNUMBER(F9),ROUND(G9-G8,2),"")

    I then copied G9 and H9 into the column G and H boxes below row 9 as far as I might need to go.

    The converted values then show up in column H. After testing, column G could be hidden.

    A spreadsheet with just the G and H formulas could be saved, and the report could be pasted into a copy of that spreadsheet each time.

    Other adjustments could be made, such as to allow more columns in the report.

     Of course if I knew VBA, then I might prefer the additional flexibility that could allow.


    Now getting trickier (and more obscure), here are an alternative pair of formulas that could go to the right of the running balance column on the spreadsheet, even if the Running Balance is not in column F.

    =IF(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4))),INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)),INDIRECT(ADDRESS(ROW()-1,COLUMN(),4)))

    =IF(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4))),ROUND(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4))-INDIRECT(ADDRESS(ROW()-1,COLUMN()-1,4)),2),"")

    So those two formulas could replace the first two, but they are not restricted to being pasted into G9 and H9 before being replicated.





    • Edited by Cal LearnerModerator Monday, September 10, 2012 10:48 PM added more obscure relative addressing formulas
    Monday, September 10, 2012 8:25 PM
    Moderator
  • the better option for my problem is that proposed by Cal 

    Yes I agree, it is best to use the running toal method, which of course gets around the problem of not knowing

    what the corresponding exchange rate was at the time.

    I cannot see any reason why you've got 15 decimal places in your calculations, unless the cells were formatted for number of places but I'' try and recreate it.

    What format were the cells in ?


    • Edited by AshleyST Tuesday, September 11, 2012 10:02 AM
    Tuesday, September 11, 2012 7:22 AM
  • Wow, they look fun, I will try and understand them later, I've not got any experience of using indirect so it may take me a while.
    Tuesday, September 11, 2012 6:55 PM
  • I can reproduce the problem

    Open Excel 2010

    Cells are all in general format

    Open a Money exported CSV

    To the right of the running balance column insert a formula to work out the transaction value

    =G9-G8

    Copy formula down

    Use Increase Decimal to expand number of decimals showing for the formula results

    At least 8 of the first 30 are displaying the error.

    Tuesday, September 11, 2012 7:02 PM
  • I've just tried this on another PC running Excel 2010 using the same CSV input file and got exactly the same result, right down to the cells with the extra decimals.

    At least it was consistent.

    Tuesday, September 11, 2012 7:10 PM
  • Sorry, I thought you were getting the 15 decimal places by default.

    Using Increase Decimal or indeed setting the number category cell to large decimal places can do that.

    It depends on the result of the sum and the total number of digits being shown from what I remember.

    As you've seen, you can easily create it by getting a 2 decimal place result and then increasing the decimal places to 15.  At 14 decimal places the result is say 0.49000000000000 but as as soon as you push it to 15 places you get

    0.489999999999995 

    The much bigger problem in Excel has always been the loss of precision with very large decimal places, typically when used in scientific calculations. 

    Quite a good explanation about Excel number precision here, 

    http://blogs.office.com/b/microsoft-excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx 

    • Edited by AshleyST Wednesday, September 12, 2012 10:42 AM
    Wednesday, September 12, 2012 7:42 AM
  • Excel was using 15 decimal places by default, Excel was subtracting two simple numbers, each with only two digits after the decimal place and giving the wrong answer, well wrong to me anyway.

    I was not interested in working to any more than two decimal places, I only used the increase decimals options to investigate why the simple if formula was not giving the expected result, see earlier post on Sunday, September 09, 2012 6:23 AM

    =IF(F8=H8,"OK","Not OK")

    The imported CVS file only had two digits after the decimal place so I could not work out why after some simple maths I got the wrong totals.

    -74.99 - -74.50 was giving a result that looked like -0.49 but the above IF was giving me 'not OK', using increase highlighted the -0.48999999999995 and not -0.49.

    The blog post does provide an explanation, however, I have never encountered this problem previsouly and I use excel most days for simple performance data analysis, pivotables and graphs etc.  I suspect it is the double-negatives that are causing the floating point precision problems.

    Thank you for your support

    Friday, September 14, 2012 5:49 AM