Use the XMIM software program to create an XMIM query containing the desired formula. Use a SHOW-WHEN to create the query. REPEATED will not work in the SHOW block.
At the top of the query there are special comments “#” that get parsed.
Example:
#formula_value index=1, relation=FORM.NG, column=Mid #formula_value index=2, relation=FORM.NG, column=Close SHOW 1: (High of NG + Low of NG) / 2 2: Close of NG WHEN Date is within 3 months
In the example above, the following definitions apply:
#formula_value index=1, relation=FORM.NG, column=Mid
where:
#formula_value – tells the program to use this line as a formula definition.
index=1 – assigns this formula definition to the corresponding SHOW line. Looks like the label but it is really the nth show attribute.
relation=FORM.NG – assigns the formula relation name
column=Mid – assigns a column name.
An existing MIM column name must be used. See the column names available in the “Column Catalog” located at: http://customers.lim.com/menu/column_catalog.htm for a complete list.
Save the query in a file in the $LIMHOME/formulas/queries.
The name of the formula file must follow the naming convention: name_ordernumber.mql
where:
name is any name to easily identify the file.
ordernumber is the sequence order the program should execute the query file in. Sequences are important when formulas are dependent on other formulas. See the section called “Creating Nested Formulas”.
Formula Query File Name Example:
$LIMHOME/formulas/queries/johndoeformula_0.mql $LIMHOME/formulas/queries/janedoeformula_1.mql
where:
johndoeformula and janedoeformula – names assigned by the user (follow XMIM naming conventions).
_0 – user assigned name is followed by an under bar and number. The number designates the numbering order for nesting purposes.
mql – this extension must be used for the formula loader software to recognize the file.
A nested formula is a formula that uses another formula. To create
nested formulas you must create separate formula query files. Create
each formula and then name the formula file using the
ordernumber to specify to the program which formula file
should run first.
For example. Suppose you wanted to create a formula that generates the sum of the high and low of symbol (relation) NG. Then you wanted to use that formula in another formula to calculate the midpoint for NG. First we will create the sum formula.
#formula_value index=1, relation=FORM.NG.SUM, column=Close SHOW 1: (High of NG + Low of NG) WHEN Date is within 3 months
We will save this file as $LIMHOME/formulas/queries/demo_0.mql. Next, we will create the formula file to calculate the midpoint using the sum formula.
#formula_value index=1, relation=FORM.NG.MDP, column=Close SHOW 1: FORM.NG.SUM / 2 WHEN Date is within 3 months
We then save this file as:
$LIMHOME/formulas/queries/demo_1.mql
Finally we run the formula loader program. The program will
execute the demo_0.mql file first loading the formulated
data points into the rel_col Close of
FORM.NG.SUM. Then the program executes the
demo_1.mql file and loads the results into the
rel_col Close of FORM.NG.MDP.
The following script runs in a shell prompt. It does not print anything out. See the log file in the formulas/log directory for all log file information. When the script stops executing, pending any errors, the data is loaded and can be accessed from the server using any of the LIM software tools.
$ run.formula_load