none
Transposing account figures from current prices to fixed priced RRS feed

  • Question

  • I am a not very experienced user of VBA. I am an economist who wants to develope tools for economic analysis of municipal account data. Basically it is a large amount of accountdata where the first step is to convert from current prices to fixed prices.

    The basic structure of data is as follows;

    Input
    Account number Account Name Function Item Y 2012 Y 2013 Y 2014 Y  2015
    1811 Sentraladministrsjon 200 1234 900 346,45 1 134 555,67 1 345 633,97 1 456 923,52
    1811 Sentraladministrsjon 201 2345 234 987,34 345 982,76 586 562,97 764 834,97
    3456 Helse 303 2456 234 456,45 346 234,97 456 789,34 532 978,67
    3456 Helse 303 4627 87 234,92 94 345,78 95 672,34 97 825,87
    4567 Kultur 534 2345 1 734,37 1 855,86 2 583,84 5 674,75
    4567 Kultur 534 2456 234 567,98 348 963,89 444 329,87 673 492,62
    4567 Kultur 534 4627 23 482,56 25 683,45 27 543,59 30 816,38
    2113 Idrett 210 4563 383 732,56 423 789,67 562 345,89 666 345,87
    4562 Barnehage 456 3578 3 456 983,36 4 937 285,89 5 439 725,38 5 678 984,78
    Y 2012 Y 2013 Y 2014 Y  2015
    Price index 1,1044 1,0671 1,03 1
    In 1000 kr 1000
    Output
    Account number Account Name Function Item Y 2012 Y 2013 Y 2014 Y  2015
    1811 Sentraladministrsjon 200 1234 994 1211 1386 1457
    1811 Sentraladministrsjon 201 2345 260 369 604 765
    3456 Helse 303 2456 259 369 470 533
    3456 Helse 303 4627 96 101 99 98
    4567 Kultur 534 2345 2 2 3 6
    4567 Kultur 534 2456 259 372 458 673
    4567 Kultur 534 4627 26 27 28 31
    2113 Idrett 210 4563 424 452 579 666
    4562 Barnehage 456 3578 3818 5269 5603 5679

    Years (Columns) will max be 10, rows (account enteries) can vary with many thousands from municipal to municipal


    Output: Account figure(Fixed Price)=Account figure(Current Price)Year2011*priceindexYear2011

    Output account figures should be in 1000 kr with no decimals.

    I have Office365 and Widows 10.

    I would be very grateful if somebody can give me a hint on how I can write the code for a challenge like this. 

    Friday, December 25, 2015 8:54 AM

All replies

  • Re: converting raw data

    You will not have to use VBA (programming code) as formulas will do the job.
    Any data brought into Excel must be "cleaned" before use.
    In your case, the data has spaces in it.  Remove them using Edit | Replace.

    See formula example below.
    Note: In the raw data, the spaces have been removed and
    (for my use) the commas replaced with decimal points.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:40 PM
    Friday, December 25, 2015 3:16 PM
  • Yep, Jim's approach is correct. You should also review all the accounting functions available as some of them may save you a lot of work, not for this problem, but for other things. See Formulas, Financial.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Sunday, December 27, 2015 5:26 AM