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:
- 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!
Leave a Reply