Qlik Community

Ask a Question

FinServ

Announcements
Login changes complete, Login with email not username. READ DETAILS/WATCH VIDEO
cancel
Showing results for 
Search instead for 
Did you mean: 
PaulVanSiclen
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
mmonfort
Creator
Creator

Hi Paul

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

I am getting this error though when loading the Nasdaq data....Nasdaq Error.PNG

any ideas on how to fix?

The other files read fine.

thank you!

mmonfort
Creator
Creator

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

AndrewMcIlwrick
Luminary Alumni
Luminary Alumni

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

PaulVanSiclen
Employee
Employee
Author

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

Video Link : 3618

brian_booden
Luminary Alumni
Luminary Alumni

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.

Finance Toolkit OHLC.png

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

http://branch.qlik.com/projects/showthread.php?684-OHLC-and-Candlestick-Finance-Extension-for-Qlik-S...

If you have any questions, just let me know.

Regards,

Brian

rbecher
MVP
MVP

Hi all,

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

http://branch.qlik.com/projects/showthread.php?694-Qlik-Sense-Stock-Quotes

- Ralf

Vizlib Head of R&D
Not applicable

Hi All,

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


Can you please help out?

Reload Failure.JPGButton.JPG

robert_cooper_a
Contributor
Contributor

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

Sajid_Mahmood
Creator
Creator

PaulVanSiclen
Employee
Employee
Author

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.