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

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.

# 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) end % Compute Payoff of European option Payoff = max(100-S,0) * Discount % Set hedge portfolio Pi to value of payoff Pi = Payoff end

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:

## Step 2: Format Data

The data for the Goole Motion Chart has to be

- : scenario name
- : time
- : numeric value or category
- – … : (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):

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! <–**

Done. Have fun browsing and exploring the data!

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

http://tech.fortune.cnn.com/2010/12/21/from-laughing-to-judging-in-fifty-years-the-evolution-of-televised-emotion/

The following link is for our new Geographical Trend Compass

(Earthquake in Japan – Mag vs Depth vs Time):

http://www.epicsyst.com/test/v2/japan-earthquake

Link on OPEC oil indicators:

http://www.epicsyst.com/test/v2/libya-oil/

Link on Ads Monitoring on TV Satellite Channels during April 2008.

Pick Duration (Ads daily duration) vs Repeat (Ads repetition per day).

http://www.epicsyst.com/test/v2/tv-monitor/

Link on UK Master Card vs Visa performance :

http://www.epicsyst.com/test/v2/mastercard_vs_visa/

Links on Funds:

http://www.epicsyst.com/test/v2/best_exchange_traded_funds/

http://www.epicsyst.com/test/v2/us_insurance_funds/

http://www.epicsyst.com/test/v2/aaim_fund_performance/

Link on other KPIs :

http://www.epicsyst.com/test/v2/hospital_performance/

http://www.epicsyst.com/test/v2/international_insurance/

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

http://www.epicsyst.com/test/v2/EarthQuakeinChile/

This a link on weather data :

http://www.epicsyst.com/test/v2/aims/

This is a bank link to compare Deposits, Withdrawals and numbers of

Customers for different branches over time ( all in 1 Chart) :

http://www.epicsyst.com/test/v2/bank-trx/

Misc Examples :

http://www.epicsyst.com/test/v2/airline/

http://www.epicsyst.com/test/v2/airline2/

http://www.epicsyst.com/test/v2/stockmarket1/

http://www.epicsyst.com/test/v2/football/

http://www.epicsyst.com/test/v2/swinefludaily/

http://www.epicsyst.com/test/v2/flu/

http://www.epicsyst.com/test/v2/babyboomers/

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

http://www.epicsyst.com/main3.swf

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 :

http://epicsyst.com/trendcompass/princeton.aspx?home=1

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.

http://epicsyst.com/trendcompass/FreeVersion/TrendCompassv1.2_DotNet.zip

All the best.

Amr Salah

Trend Compass Team

Epic Systems

http://www.epicsyst.com