2 Replies Latest reply: May 15, 2014 9:53 AM by Mags77 _ RSS

    Filling in Data

    Mags77 _

      Hello All - V 7.0 user here, brand new to the forum, but have been using Monarch for a few years now.  I'm looking to see if there's any way that Monarch would be able to fill in some data for me.  I have a list of closing share prices for mutual funds that appears as below:

       

      FUND   PRICE DATE        SHARE PRICE

      1     980102            15.99000000

      1     980105            16.04000000

      1     980106            15.88000000

      1     980107            15.84000000

      1     980108            15.68000000

      1     980109            15.29000000

      1     980112            15.42000000

      1     980113            15.62000000

      1     980114            15.71000000

       

      The pricing skips over any weekends or holidays, what I'm looking to do is set something up where I can get the weekend dates filled in, and the share price from the previous day to carry down, as in the mock up below:

       

      1     980102            15.99000000

      1     980103            15.99000000

      1     980104            15.99000000

      1     980105            16.04000000

      1     980106            15.88000000

      1     980107            15.84000000

      1     980108            15.68000000

      1     980109            15.29000000

      1     980110            15.29000000

      1     980111            15.29000000

      1     980112            15.42000000

      1     980113            15.62000000

      1     980114            15.71000000

       

       

      Any ideas?

        • Filling in Data
          joey

          I have an idea, and it proves that you CAN do it in Monarch, but it doesn't mean you SHOULD do it in Monarch. 

           

          Note that this solution uses External Lookups which weren't in V7, so I won't take the time to list it here.

           

          There's also a few key issues to address:

          1)Do you want values filled in before/after the range date in your report?  For example should the report carry the last price down to 12/31/2007?

           

          2)How to handle January 1st, if the previous year's data is not available.

           

          3)Whether to handle leap year or not?

           

          4)What is the largest stretch that data would need to be carried down?  Is a 5 day weekend/holiday the largest time span that should be considered.

           

           

          My opinion is the program that generates this report is the best place to handle your need.  But Monarch might be able to handle it if you had V8 or above.

          • Filling in Data
            Mags77 _

            Thanks for the reply Joey.  Unfortunately, there's no program that's generating the list, it's just a dump out of a price file, which is why I was hoping that Monarch could help, but alas I'm stuck with the 7.0 for the time being.  This is kind of what I thought but wanted to run it by the experts out there, thanks again!