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

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:

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):

+ the Horizontal List extension added to this post

- 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

• 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.

• Re: Finance Toolkit

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

• Re: Finance Toolkit

Cant remember what I did but I figured it out

• Re: Finance Toolkit

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

• Re: Finance Toolkit

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

• 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?

• 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?

• 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...

• Re: Finance Toolkit

Hi Paul

Great app. Definitely some things in here I can use for my investments app.

any ideas on how to fix?

thank you!

• Re: Finance Toolkit

Don't worry - have sorted. Found the post re the initial settings for Qlik Sense and set it to 0 for the StandardReload... once set it all works fine

• Re: Finance Toolkit

Paul. Any possibility Qlik could start publishing videos onto your own servers. YouTube is blocked for us and many other organizations. Thanks.

• Re: Finance Toolkit

Great point, thanks for the note.  as a temporary fix, here is the video posted directly to Qlik Community:

• Re: Finance Toolkit

Hi Paul / all,

I've now added an additional sheet to Finance Toolkit called OHLC and Candlestick that utilises my new Qlik Sense Extension.  I have attached an updated qvf.

Note that you you will need to download and install the extension from Branch:

If you have any questions, just let me know.

Regards,

Brian

• Re: Finance Toolkit

Hi all,

I created an extension to pull in stock quotes from Yahoo! Finance:

- Ralf

• Re: Finance Toolkit

Hi All,

I am having problems to load the application and also make use of the buttons.

• Re: Finance Toolkit

Hi Sarb,

I had the same issue - looks like Nasdaq must have tweaked its website since November. Try updating the "From" part of the load statement to say "table is @6" rather than "@5".

• Re: Finance Toolkit

• Re: Finance Toolkit

Hi Sajid,

Recognizing that there is a setup process for this particular app (because I am utilizing many of the great extensions being built on branch.qlik.com), I have posted the app on our demo site.

Qlik Demos: See Qlik Sense in Action | Sense-Demo.Qlik.Com

Hope you find this useful.

• Re: Finance Toolkit

Hi, Paul!

I noticed that the Nasdaq Stock Price dashboard isn't available on the Qlik Demo.  Also, I can't seem to get it to work by downloading the files in the original posting.

Is there a way to make it work?  Or was the source webpage with the ticker information taken down?

• Re: Finance Toolkit

Hi Fabio,

I have heard of others running into this lately and have not had the time to trouble shoot.  You can easily rebuild this though by navigating to any html table with stock prices and pulling into Qlik.

To troubleshoot this app, i would recommend going into the script/data connection and finding the linked page it is pulling data from, then researching if that page is still up.

Best regards,

PVS

• Re: Finance Toolkit

Hi Paul,

Are you planning to update this great financial tool app with additional features, utilizing the new features of Qlik-Sense 3.0 which will be released shortly.

Regards

Sajid

• Re: Finance Toolkit

Hello Sajid,

Maybe someday but I can't tell you when it will happen.  It will definitely be interesting to experiment though.

Thanks for the feedback.

PVS