23 Replies Latest reply: May 20, 2016 2:54 PM by Paul Van Siclen RSS

    Finance Toolkit

    Paul Van Siclen

      Hello Qlik Financial Services community members,


      Qlik Sense v2.1 was recently released and this new version provides a method of creating/editing variables.  With this new functionality, I decided to take a shot at consolidating some of the tools I have created into one killer Finance App.  The app has been pretty popular so far so I added some additional functionality and am posting it here.


      Here is a YouTube Video/Webinar that walks through the app:






      A brief explanation of the tabs in the application:


      1.  Introduction to the Associative Model

      This is a simple view of several list boxes which demonstrate the ability of the user to drive analysis through an unlimited combination of Divisions, Products, Cost Centers and Accounts.


      2.  Essbase Expenses

      The source of this tab is an essbase extract.  Note that a QlikView connector is available on our download site which can completely streamline this process and we have several customers who have successfully implemented this.  I don't have a date when the Qlik Sense version of the connector will be available but you can copy the script from QlikView and paste it into Qlik Sense.


      3.  Stock Price Analysis

      Data captured from HTML tables provided by the Nasdaq site.  You have the ability to type in any ticker and click on the Reload button to capture a different set of data.


      4. Valuation

      This is a dynamic IRR calculation on a population of 299 different investments.  An unlimited combination of dimensions can be selected to see the IRR.  The formula being used here is =rangexirr() which is a very powerful function for this purpose.  I have used this to value a actual portfolio of earning assets in the past and it was very powerful.


      5. CAGR

      The =Rate() function calculates the Compound Annual Growth Rate of a portfolio.  Data is made up of 2 divisions (Commercial and Consumer) and 30+ business units.  The power of this analysis is that the CAGR formula is tied to the dates selected.  For example, make a selection on the last 5 quarters and compare that portfolio CAGR to the previous periods.


      6.  Amortization, Forecasting & Time Value of Money

      If you can create an accurate model for a single loan, then you can model an entire portfolio.  This is a Single Loan Model which forecasts the AUM and Interest Income over the selected term of the loan.  It is not a stretch to combine this with other data points/vintages and build a model for an entire portfolio.


      7. Break Even Analysis

      A simple formula applied to the "profit equation" displayed in a "Line" which is an important step to the next few tabs.


      8. Option Valuation & Greeks

      Demonstrating the inputs related to the Black-Scholes model (which is a built in function of Qlik).  The chart is simply demonstrating the impact of changing the volatility assumption on the price.  The higher the volatility, the higher the value/price.


      9.  Simulation

      The total profit equation with one of the variables (Price Per Unit) assigned to a normal distribution with a Standard Deviation assumption.  This entire simulation is calculated in memory and becomes very interesting when you add a 0 to the # of simulations (1000; 10,000; 100,000 simulations all run very quickly on my laptop).  Used in practice, you may consider putting this formula into the script, build a table, store the model in a qvd (with other scenario runs) and reload all of the data into memory.


      10. DataMarket - FX Conversion

      Demonstration of FX Conversion with currency data from Qlik DataMarket.




      To get all of this to work, you need Qlik Sense Desktop v2.1 and to follow these instructions (can be completed in 10-30 minutes, depending on your familiarity with the steps):


      1.  Download the app called FinanceToolkit20150916.qvf into your Qlik/Sense/Apps folder

      2.  Download/Install the following Charts extensions from QlikBranch in Qlik/Sense/Extensions folder




      + the Horizontal List extension added to this post


      3.  Download these source files in a directory of your choice (i.e. c:\FinanceToolkitData)

          - PortfolioData.xls,

          - Data.xls,

          - Dim.QVD

          - Fact.QVD


      4.  Go into the "Data Load Editor" of the app and re-point each data source to the directory you put your source files in