How can I visualize my model data in an interactive webpage?


A brilliant website is the perfect example for user friendly interactive data visualization: Gapminder. This website presents statistical data about the countries of the world and how it changes in time. A user-friendly AJAX user interface allows you to get gain new insights exploring this data. They have done a great job. Google bought this technology and created Google Public Data and Motion Charts. Read in the following, how you can utilize this for your own data.

Visualize Data in Finance

Motion Chart from Gapminder

In finance, a large part of the work is data visualization. Historical prices, portfolio positions and simulation model demand for an interactive dashboard for data analyzes. There are many offline tools exploring data, e.g. MS Excel, Matlab or R. For online data visualization, there are only very few choices which makes the Google Motion Chart interesting:

  • Pro: Cool interface, easy configuration and easy deployment using Javascript or Google Docs.
  • Cons: Bound to google servers and no source code available.

So, if you are looking for an in-house solution, you could try to build one yourself using AJAX frameworks like Google Web Toolkit or ZK. But, if you want to share your visualization in the internet, Google Motion Charts are a good start.

Google Motion Chart

Walk through for a Google Motion Chart from Excel

Step 1: Creating Data

First, we need to create some data which we want to analyze. Here, we use the computation of a variance optimal hedge strategy in ThetaML:

model VarianceOptimalHedgeOfEuropean
export Payoff, S, Delta, Pi, HedgeError

% Initialize Parameters
S = 100; Discount = 1;
sigma = 0.4; r = 0.05;
loop 50
% Update position of variance optimal hedge
Pi = Pi! - Delta! *(S!*Discount!-S*Discount)
Delta = Beta(S!,Pi!)

% Money required for hedge error
HedgeError = Pi! - E(Payoff!)

% Pass one week of time
Theta 1/50

% Update stock price process S
S = S * exp( (r-0.5*sigma^2)*@dt + sigma*sqrt(@dt)*randn() )
Discount = exp(-r * @time)
% Compute Payoff of European option
Payoff = max(100-S,0) * Discount
% Set hedge portfolio Pi to value of payoff
Pi = Payoff

This model generates a Monte Carlo simulation for the stock price process S, the option payoff, and the value of the hedge portfolio Pi. After running this Model in Theta Suite, the Result Explorer opens and we can export the simulated data to Excel:

Create Excel file from ThetaML

Step 2: Format Data

The data for the Goole Motion Chart has to be

  1. : scenario name
  2. : time
  3. : numeric value or category
  4. – … :  (optional) numeric value or category

Thus, we have to reformat the exported data: Open the Result Excel Sheet and use the following formulas on the first empty sheet (sheet 1):

Excel Formulas for Reformating

index:          1 (rows 1:51), A2+1 etc (rows 52:5101)
Time Step:      =IF(MOD(ROW(A2)-1,51)=0,52,MOD(ROW(A2)-1,51)+1) 
Scenario Name:  =CONCATENATE("Scenario_",A2) 
Time:           =TODAY() + 365*INDIRECT(CONCATENATE($D2,"!","A",$B2)) 
S:              =INDIRECT(CONCATENATE($D2,"!","B",$B2)) 
Delta:          =INDIRECT(CONCATENATE($D2,"!","C",$B2)) 
Payoff:         =INDIRECT(CONCATENATE($D2,"!","D",$B2)) 
HedgeError      =INDIRECT(CONCATENATE($D2,"!","E",$B2)) 
Pi              =INDIRECT(CONCATENATE($D2,"!","F",$B2))

Copy these formulas until row 5101 (for 100 Scenarios).

Step 3: Publish ThetaML Result Data with Google Docs

Now, create an Excel sheet which only contains the values of “Sheet 1”. This can be imported into google Docs. After importing, select “Insert->Gadget->Charts->Motion Chart”. Select Range e.g. “Data!A1:G511” for the first 10 scenarios or “Data!A1:G5101” for all 100 scenarios as data of the chart. Finally, we get a sheet like:

–>Try Motion Chart Example Here! <–

Google Motion Chart from ThetaML

Google Motion Chart: Delta vs. S

Done. Have fun browsing and exploring the data!


Comments RSS
  1. Amr Salah

    I have the pleasure to brief you on our Data Visualization software
    “Trend Compass”.

    TC is a new concept in viewing statistics and trends in an animated
    way by displaying in one chart 5 axis (X, Y, Time, Bubble size &
    Bubble color) instead of just the traditional X and Y axis. Discover
    trends hidden in spreadsheets. It could be used in analysis,
    research, presentation etc. In different business sectors, to name a
    few we have Deutsche Bank, NBC Universal, RIM, Vanguard
    Institutional Investor, Ipsos, Princeton University as our clients.

    NBC presentation on TED using Trend Compass exported Videos. Now on
    CNN Money / Fortune (Dec 21st,2010):

    The following link is for our new Geographical Trend Compass
    (Earthquake in Japan – Mag vs Depth vs Time):

    Link on OPEC oil indicators:

    Link on Ads Monitoring on TV Satellite Channels during April 2008.
    Pick Duration (Ads daily duration) vs Repeat (Ads repetition per day).

    Link on UK Master Card vs Visa performance :

    Links on Funds:

    Link on other KPIs :

    Link on Chile’s Earthquake (Feb 27th 2010):

    This a link on weather data :

    This is a bank link to compare Deposits, Withdrawals and numbers of
    Customers for different branches over time ( all in 1 Chart) :

    Misc Examples :

    This is a project we did with Princeton University on US unemployment :

    A 3 minutes video presentation of above by Professor Alan Krueger
    Bendheim Professor of Economics and Public Affairs at Princeton
    University and currently Chief Economist at the US Treasury using
    Trend Compass :

    You can download a trial version. It has a feature to export
    EXE,PPS,HTML and AVI files. The most impressive is the AVI since you
    can record Audio/Video for the charts you create.

    All the best.

    Amr Salah
    Trend Compass Team
    Epic Systems

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.