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.
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.
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.
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)
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
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.
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?
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...