INVENTION OF DYNAMIC CHARTS

In 1994, I wanted to explain the dynamic properties of the term structure to my undergraduate Investments class. I thought of the idea of an animated chart showing a sequence of monthly term structure graphs (a term structure "movie"). But how could I implement this? 

My first attempt to do this was to export a series of Excel screen shots to PowerPoint and then do a PowerPoint slide show. Unfortunately, I couldn't get the Excel graphs to line up exactly from slide to slide, so the graphs would jump as much as the term structure and you couldn't get a real sense of the motion of the term structure itself.

My second attempt was to make each month's term structure a separate line on a single chart. I had 21 years of monthly data, so there were 21 * 12 = 252 separate lines on the graph! Obviously, this looked like spaghetti. The trick that I used to isolate a single month at a time was to format all of the lines so that they were invisible (no marker and white line color). However, even an invisible item becomes visible it is selected and you can cycle through all of the objects on the chart in order by hitting the "up arrow." This trick worked and I used the term structure "movie" in my Fall 1994 class. Check it out for yourself. Click here to download it. To use it, click anywhere on the graph and then hit the "up arrow" a bunch of times. Click on the history tab to see 21 years of term structure history. But this approach was not very elegant. For one thing you couldn't tell what month you were on, so you couldn't tell a story about the political and economic determinants of the term structure at that time.

My third attempt in 1995 benefited from the release of Excel 95. A new feature of Excel 95 was the "forms" toolbar which allowed you place a wide variety of graphical input controls directly on the spreadsheet. One of these controls is a "spinner" (up-down arrows). I thought of the idea of using spinners to control the inputs to a chart and Dynamic Charts were born! Now the chart would have a single line on it and the spinner combined with a horizonal lookup function would control which month of data was displayed. It worked great. I soon found that dynamic charts worked well for comparative statics (such as Black-Scholes comparative statics) and wide variety of other purposes. I also found that other input controls were very helpful. For example, I used drop-down lists to select the asset combinations that go into option spreads and combinations payoff diagrams. I used four dynamic charts in my Spring 1995 class: (1) term structure "movie", (2) Black-Scholes comparative statics, (3) option spreads and combinations, and (4) portfolio optimizer. All of them were very interactive and worked well in the classroom.

 In 1996, I posted these four dynamic charts on my web site and that began a chain of events which lead eventually to this Spreadsheet Modeling textbook and CD-ROM series. I have used dynamic charts for six years in the classroom. I have used them both to interactively demonstrate finance concepts to the class and have had students build them from scratch. I have incorporated dynamic charts throughout the Spreadsheet Modeling series. In doing so, I have stuck with spinners, rather than the other input controls, because I believe they are the easiest to explain how to build and they are the most generally useful.

In 1999, I engaged in an e-mail dialog with the Product Manager of Microsoft Excel. Although it has been possible to create dynamic charts since the spinners were added in Excel 95, this depends on putting together two things (spinners and charts) which are currently separated. I suggested that they might want to build an option to create dynamic charts into Excel "Chart Wizard." That is, dynamic charts would simply be another chart option, just like bar charts, line charts, etc. This would make dynamic charts easier to create and much more visible option to the user. The product manager and other people in his group liked the idea very much. He indicated that it was too late to add features to Excel 10 (likely to be called Excel 2002) because the feature set of that product was already locked into place. But he would like to include it in Excel 11 (likely to be called Excel 2004). So check out Excel 2004 for built-in dynamic charts when it is released!

Craig