8 Replies Latest reply: May 15, 2014 9:54 AM by ndello _ RSS

    Getting Tricky now

    ndello _

      Can I somehow create a reference table that can be used in a lookup for calculating brokerage.

      Let me explain,

      I would have a table like this

      Commodity, floor_charge, elctronic_charge

      EURO/FUT, $1.00, $1.50

      EURO/GBP, $2.00, $1,00

       

      So In my original table, I will add a calculation field and then use the above table to calculate brokerage.

      Can this be done?

      Thanks

        • Getting Tricky now
          Nigel Winton

          Hello there

          The best way to do this would be by setting up an Excel Spreadsheet or an Access Database then use the External Links in Monarch. That way you can bring in as many columns as you need, if you use a Lookup table in Monarch you only get one column. Then again you could have 2 Lookup Tables.

          Hope that gives you enough to go on.

           

          Regards

           

          Nigel

          • Getting Tricky now
            ndello _

            Thank you, I am using the 2 lookup tables now, but will look into the linking later on.

            kind regards

            Neil

            • Getting Tricky now
              Grant Perkins

              As a rule I would tend to use an internal lookup table for something that was not going to change or not change very often (Month number to Month name for example) but seriously consider an external lookup for something that might change regularly - an exchange rate for example.

               

              One thing to consider strategically for Monarch use within an organisation is who maintains any external tables. They may of course be a by-product of the whole process from another Monarch model but they may also be produced elsewhere.

               

              Sometimes the person responsible for the lookup table may be happier simply producing a text file so it's worth keeping in mind that a simple text

              file can also be used as a viable delimited text lookup 'database'.

               

              Sounds like you're having fun and getting up to speed quite rapidly.

               

              Grant

              • Getting Tricky now
                ndello _

                Thanks Grant,

                I do have another question and hope you can help:

                Is there a limit to the number of IF statements within a cell?  Also, have you come across where the if.and.or needs to be in a certain order?

                I have this formula:

                IF(COMMODITY="EURO/FUT" .And. FLOOR_BRO="MANFIN" .Or. FLOOR_BRO="LEHBROS",Eq_Trader2*,0)

                The problem is where I have a FLOOR_BRO equal to MANFIN, however a COMMODITY of "EUBOL3M/FUT"  This combination does not meet the criteria, so it should show zero, however it shows a calculated number?So I am wondering if my formula is correct?

                Thanks

                Neil

                • Getting Tricky now
                  Grant Perkins

                  Originally posted by ndello:

                  I have this formula:

                  IF(COMMODITY="EURO/FUT" .And. FLOOR_BRO="MANFIN" .Or. FLOOR_BRO="LEHBROS",Eq_Trader2*,0)

                  The problem is where I have a FLOOR_BRO equal to MANFIN, however a COMMODITY of "EUBOL3M/FUT"  This combination does not meet the criteria, so it should show zero, however it shows a calculated number? Thanks

                  Neil /b[/quote]Neil,

                   

                  I try to avoid the and/or combinations - mostly I am lucky in not dealing with anything that needs them!

                   

                  I think you need to group the the OR values in brackets for the correct order of processing but with V8 I would tend to use the .IN. operator I think.

                   

                   

                  IF(COMMODITY="EURO/FUT" .And. FLOOR_BRO .IN.("MANFIN","LEHBROS"),Eq_Trader2*,0)

                   

                  The Help offers more examples.

                   

                  If that's not it either I am on completely the wrong track or there is something else coming into play in your data. We could toss a coin on which it is if you like 

                   

                  Have fun.

                   

                   

                  Grant

                  • Getting Tricky now
                    ndello _

                    Thanks Grant, What I did was break it down into more IF statements than I wanted, but it works fine.

                    Something like this:

                    IF(COMMODITY="EURO/FUT" .And. FLOOR_BRO="MANFIN",Eq_Trader2,IF(COMMODITY="EURO/FUT" .And. FLOOR_BRO="LEHBROS",Eq_Trader2,0)

                     

                    I will try the IN formula as well.

                    Is there a limit to the number of IF's in a formula?

                    Thanks again

                    • Getting Tricky now
                      Bruce _

                      Neil,

                       

                      What you are seeing the is the order of operations in mathematics. Equations are always calulated from left to right, brackets 1st, ANDs and ORs next, then powers (exponents), then multiplations and divisions, then finally addition & subtraction. Plus a few other tidbits I don't recall from high school, but that's the gist of it.

                       

                      Therfore your equation    

                       

                      IF(COMMODITY="EURO/FUT" .And. FLOOR_BRO="MANFIN" .Or. FLOOR_BRO="LEHBROS",Eq_Trader2*,0)[/quote]would mean that commodity = "Euro" is evulated 1st. This gives a true or false, then the AND floor = "manfin" is evalulated and if BOTH are true then it goes on.

                       

                      Anyhow Grant's idea is fantastic or you can always use brackets to change the equation order.

                       

                      EG     IF(COMMODITY="EURO/FUT" .And. (FLOOR_BRO="MANFIN" .Or. FLOOR_BRO="LEHBROS"),Eq_Trader2*,0) /quotewould force the OR statement to be done before going back to the AND statment.

                       

                      PS most other softwares will work this way, not just Monarch

                       

                      Cheers

                      • Getting Tricky now
                        ndello _

                        Thanks Bruce,

                        I did try the In as Grant stated and it works great, and it's much cleaner than mine.

                        Thanks again, and I'm sure I'll be back for something else, hopefully it will be interesting as well.