Qlik Community

Qlik FinServ

A private group designed to enable innovation, share best practices and educate QlikView users in the Financial Services industry. You will not see any content if you are not a member, you can request to join by pressing "Join the Group"

Highlighted
Employee
Employee

Finance Toolkit

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:

FinanceToolKit.png

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

     http://branch.qlik.com/projects/showthread.php?498-Qlik-Sense-Reload-Button&highlight=reload+bu...

     http://branch.qlik.com/projects/showthread.php?379-Sense-Boxplot&highlight=boxplot

     http://branch.qlik.com/projects/showthread.php?396-qsVariable&highlight=variable

+ 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

23 Replies
cluscombe
New Contributor III

Re: Finance Toolkit

Tried to add the Horizontal List extension using the HorizList.7z using Qlik Sense Server, but got an error that "the selected file is not an extension file".  Any chance you the Horizontal List extension link from Qlik Branch handy?  I was able to install the first three extensions easily.

Partner
Partner

Re: Finance Toolkit

Great video and explanation downloading and implementing for my own demos!

Sajid_Mahmood
Contributor

Re: Finance Toolkit

Most interesting application particularly for finance guys. Thanks a lot for your support

cluscombe
New Contributor III

Re: Finance Toolkit

Have steps 1-3 done, but struggling with step 4.  Getting these errors in the data load editor

Partner
Partner

Re: Finance Toolkit

Nice work - great job putting together a really useful application.  I used the workaround for the historical Qlik data as per the thread.  Is there another method required to get it to read directly from the web upon reload instead of using the external file?

Employee
Employee

Re: Finance Toolkit

@Chris Luscombe:  both of those data sources are sourced from the cloud or internet sites.  Perhaps you are not connected to the internet?

Not applicable

Re: Finance Toolkit

Hi Chris, have you uncompressed this file with WinZip or similar?

Not applicable

Re: Finance Toolkit

Thanks for this awesome app and walk-thru video.  I too am getting stuck on step 4 however.  I have modified the relevant Folder Data connections to point to the folder where I have put the data files, but I receive the error below when attempting to load...

error 1.PNG

cluscombe
New Contributor III

Re: Finance Toolkit

Cant remember what I did but I figured it out