Hi R and Axys, I’m d3.js “Nice to Meet You” (On the Iphone)

I am still definitely in the proof of concept stage, but as I progress I get more excited about the prospects of combining d3.js with R and Axys through Bryan Lewis’ really nice R websockets package (even nicer now that he has added the daemonize function).  In this iteration, I will add a cumulative growth line chart, some animation and transitions, and then javascript will ask R to calculate drawdowns.  Instead of R returning a chart like last time, R will send the results of the drawdown calculations through JSON through the websocket.  We will then use d3.js to draw a line chart of drawdowns.  This becomes really powerful when we consider the existing and thanks to Google Summer of Code soon to be added risk/return calculations offered by the R PerformanceAnalytics package.

As one last bonus at the end of the video you will see that we can get this interactive reporting experience and websocket communication all on our Iphone and Ipad.

As a quick review of what is happening

1. Axys runs a report called perhstsp.rep.
2. Axys calls the cdataset.xlsm testd3axys macro and sends performance information.
3. Excel cdataset.xlsm testd3axys macro does some very basic formatting, converts the data to JSON, creates a webpage, and opens the webpage in the default browser.
4. Browser opens the webpage and d3.js generates a bar graph of performance and then a cumulative growth line chart.
5. Browser provides a button to open a websocket with R and send the performance information originally calculated in Axys.
6. R receives the performance data through the websocket, calculates drawdown, and then sends the drawdown calculations as JSON back to the browser.
7. Browser receives the drawdown calculations and d3.js plots them as a line chart.

 

The next set of iterations will focus on cleaning up the d3.js charts and adding interactivity.

So far I have received no comments.  Please let me know what you think about this.

The list of acknowlegements is starting to get long. I really appreciate all the fine work done by Mike Bostock on d3.js https://github.com/mbostock/d3/wiki, the dedicated authors of the R package PerformanceAnalytics http://cran.r-project.org/web/packages/PerformanceAnalytics/index.html, Bryan the author of http://illposed.net/websockets.html and the example, the author of RJSONIO http://cran.r-project.org/web/packages/RJSONIO/index.html, and Bruce McPherson at http://excelramblings.blogspot.com/ for the inspirational idea.

To work through on your own, you will need the Excel file cdataset.xlsm, the Axys report perhstsp.rep, and the R code from GIST.

Best of Axys, R, d3.js, and HTML5

Axys, R, d3.js, and HTML5 all offer incredibly powerful tools for investment management and reporting, but they are not set up to synergistically interact to fill each other’s gaps and leverage each other’s strengths.  In my ideal scenario, Axys serves as the accounting system and performance calculator, R serves as the advanced financial/statistical engine, d3.js serves as the interactive reporting component, and HTML5 offers the user interface and ties everything together through websockets (nicely demoed here).  After working and suffering with Axys for 12 years, I am amazed that it all seems to be coming together.  I provided a bare proof of concept for Axys to d3.js in my post Axys to d3.js Error Catching and Formatting.  Now let’s extend that to R and websockets through the generously contributed R websockets package.  I have borrowed very heavily from the author's Youtube example presented in

In this proof of concept, Axys will calculate performance and send to Excel through a graph macro which creates JSON and an html page (thanks Bruce http://excelramblings.blogspot.com/).  The html page contains javascript and d3.js to produce a simple bar chart.  Now we add a button to take the JSON created by Excel and embedded in our html and send it to R.  R will produce a charts.PerformanceSummary chart and send it back as jpeg to the html page.  The html page will receive the image and replace the d3.js bar chart with the image.

Going forward I will only use R as the statistical engine and continue to rely on d3.js for the interactive reporting.  How far I go with this depends heavily on user response.  Please let me know if you would like me to continue down this path.

The list of acknowlegements is starting to get long.  I really appreciate all the fine work done by Mike Bostock on d3.js https://github.com/mbostock/d3/wiki, the dedicated authors of the R package PerformanceAnalytics http://cran.r-project.org/web/packages/PerformanceAnalytics/index.html, Brian the author of http://illposed.net/websockets.html and the example, the author of RJSONIO http://cran.r-project.org/web/packages/RJSONIO/index.html, and Bruce McPherson at http://excelramblings.blogspot.com/ for the inspirational idea.

To work through on your own, you will need the Excel file cdataset.xlsm, the Axys report perhstsp.rep, and the R code from GIST.

d3.js Chart of Axys Performance

Please see Axys to d3.js Error Catching and Formatting for a much better version.

Way back in January I made some amazing (is it sad that I consider this amazing?) discoveries as I tried to push the limits with Advent Axys reporting and even integrated R with Axys in R in Axys (Impossible Dream).  Now I have accomplished what I believe to be even more exciting and potentially useful by  combining the nice Excel and d3.js work done at http://excelramblings.blogspot.com/ with 20 year old Axys technology (nicely improved by http://viabinary.com/ViaBinary/Home.html).  For those of you who know reporting in Axys, I’m sure you share my excitement.  Please let me know your thoughts.

To prove that I’m not dreaming, here is the screencast.

To try it for yourself, please download the perhstsp.rep Axys report and a stripped down version of cDataSet.xlsm (see the full version for more http://ramblings.mcpher.com/Home/excelquirks/downloadlist) .  If you would just like to see the final html output then go to http://bl.ocks.org/3106445.

Thanks to Mike Bostock http://bost.ocks.org/mike/for all his incredible work on everything, but especially d3.js.  Also, thanks again to Bruce for his stimulating work with Excel.

Production Quality Bullets?

Incrementalism can lead to good (in design) or bad (in politics) outcomes.  Subtle changes to reports can often yield significant benefits.  I hope I was able to achieve a positive experience with just a couple modifications to Better Bullets. To achieve this result, please use the newest excelgrf_macros.xlsm.

From TimelyPortfolio

I think the next version might take our formatting abilities a little too far, but I will show it just as another alternative.

From TimelyPortfolio

So far I have not heard any comments or suggestions.  Please send them my way.  I am aware that the instructions are fairly sparse. I hope to do a screencast very soon.

Few’s Bullets in Axys Reports

Just a couple of days ago, I would have said what I am about to show was impossible in Axys reports.  However, with the very fine work from Fabrice Rimlinger http://sparklines-excel.blogspot.com/ and a little stretching of the boundaries, I have been able to get bullet performance graphs in an Axys performance report.  For more on Stephen Few’s innovative bullet charts, see http://www.perceptualedge.com/blog/?p=217 or for his books see

Just like in Axys Combo Incremental Improvement, we will run a custom report (in this case an amended Axys perhist perhstsp.rep) and use excelgrf_macros.xlsm to get our new macro sparkbullet.

From TimelyPortfolio

From TimelyPortfolio

And we get something amazing but not all that pretty.  I’ll take it though.  Please see the updated Better Bullets for an even better version of this chart.

From TimelyPortfolio

Axys and Graphic Design

I am sure most of my normal readers discovered a long time ago that I have no degree in graphic design, but that does not mean that I cannot try.  To further the proof of concept and hopefully inspire some thought, here is an initial pass at graphic design on Axys Combo Incremental Improvement.

From TimelyPortfolio

Axys Combo Incremental Improvement

As I experiment more with Power of Sparklines in Advent Axys, I thought my first real challenge would be to get two charts with one report.  In my mind, client reporting generally contains four major sections:

1. Performance
2. Activity Summary
3. Historical Value
4. Allocation/Current Holdings

For this incremental improvement, I will work on 2 (Activity Summary) and 3 (Historical Value).  The new Excel file can be downloaded at excelgrf_macros.xlsm and our fancy new Axys report at actimval.rep.  I accept no credit for the code in the report since most comes from the standard Axys activity.rep and mval.rep.

The steps will be very similar to those taken in Power of Sparklines in Advent Axys.

First run actimval.rep in Axys reports.

From TimelyPortfolio

Then click graph

From TimelyPortfolio

which brings up the second graph dialog box where we can input the location and name of the Excel file and our new macro.

From TimelyPortfolio

And hopefully you will get something similar to this. Not pretty but certainly a step in the right direction.

From TimelyPortfolio

Currently, most of this is more proof of concept and experimentation, but by the end of all this, I foresee some mighty clean and impressive report packages.  Please comment and contribute.  I know this is pretty domain specific, so let me know if you are out there.  As far as I know the only folks professionally pursuing Axys custom reporting besides Advent are http://cssi.org/ and http://www.isitc.com/.

Power of Sparklines in Advent Axys

I recently discovered the amazing work of Fabrice Rimlinger at http://sparklines-excel.blogspot.com and thought of what I believe is a novel way of using the Microsoft Excel integration with Advent Axys reports to potentially achieve a dashboard style report.  This would be far more visually compelling and informative than anything else I have seen in Axys.  For this report, we will just use the standard Axys Portfolio Summary Report and then an Excel macro http://www.box.com/s/9jenb341qmv4iaoeh5uy.  Please also install the appropriate Excel add-in from http://sparklines-excel.blogspot.com.

I know the audience for this will be more limited than my general audience.  I might move these Axys report discussions to my incomplete and defunct http://axysreporting.com site.

For the R lover, I am hoping to also incorporate the unbelievable RExcel from http://rcom.univie.ac.at/.