The Formulas window in SimCorp Dimension can be used in various windows of SimCorp, from imports in a DFS, a Portfolio Calculation over to View Transactions. In this piece, we will look at some tips on how best to construct a formula and some general recommendations.
Before starting to write the code of your formula, it is always a good idea to use variables. You define a variable at the beginning of the formula by giving it a short name (no space), followed by a := then the field or function or small code, and end it with a semicolon (;).
For example:
Por := {160: Portfolio};
TD := today();
This not only allows you to reuse variables throughout the formula, but also makes it easier to read, more compact, and reduces the number of calls to the database for the same information.
While speaking about good practice, we highly recommend that you always document your work by writing a few lines at the top of the formula outlining what the formula does, and what was the reason for choosing certain conditions or rules. Even though the code is self-explanatory, no one will know your thought process behind the build and why you chose that field, those functions, or did that calculation. You can create notes by adding two forward slashes at the beginning of each line of comment. SimCorp will comment out the entire line (there is no end-of-comment).
For example:
//This formula was built to ensure…
//We chose this calculation because…
The first challenge you might face is the data type. In a formula, you often mix different types of text, numbers, and dates. Unfortunately, this is extremely important to get right, otherwise, you cannot even save the formula. For example, if you construct a date from different fields, let’s say a year from one field and a month and date from another. When you combine these two, make sure you convert that using the todate() function if you want to use it in a get-function, and use totext() if you want it to be the result used in the formula (provided the formula returns text). It is not unusual in a formula to use a mix of different totext(), tonumber() and todate() functions.
We mentioned get-functions in the beginning. These are special functions that can fetch data from other tables based on Security ID (or rather the internal key – IK), Party ID, etc. Note not all these functions are called ‘get’, but they work similarly to fetch or calculate data. A right-click on a get-function and selecting ‘Help’ is a great way to learn more about the function and what it does. As many of these functions use the internal key rather than the identifier itself, you can use getsecik() and getparik() within the function.
To avoid runtime errors you can use the try x catch(y) function. This allows you to use a set value (y in this case) if the function returns an error. One example when using the getsecik() as described above is to add a try .. catch around it and return 0 in case the lookup fails, meaning you would use an internal key of 0 rather than have the formula fail:
try getsecik({Security ID};0) catch(0)
Formulas are very technical in nature, so are not something you would use as widely as for example segments which we discussed in tip #15. This must be the reason there are unfortunately no record-based authorizations available for formulas (see tip #10), meaning you cannot restrict access to individual formulas. You will need to restrict access to all formulas and then set up a process to handle changes to any formulas.
The last item we will look at is the Used in function under the file menu. This is a great tool when you are changing a formula. Most are only used in one setup, but for those that are used in multiple setups, it is always a good idea to run this function for any changes you make before promoting them to testing and production. You don’t want changes made in one area affecting another because of a small change to a formula. And then, of course, it is helpful to clean up retired functionality to help maintain a pristine environment.
Thank you for your attention, we hope you find inspiration in this and, as always, welcome your feedback and experience.