Monday, September 29, 2008 was an ugly day. How ugly do you ask? The Dow Jones Industrial Average (DJIA) plunged nearly 800 points with violent moves all day as the House rejected the bailout plan. Ouch babe. This one will unfortunately be one that will be remembered.
Yahoo Finance provides historical data for Equities and Indices such as the DJIA. What do we do with a bunch of DJIA closing and their associated dates? Hrmm… graph anyone?
This graph is fairly easy to create but it does not tell us much. After all, I think it is pretty obvious that the US economy has grown since 1928… I am thinking that it would be a really nice & spiffy if we were able to create an interactive version of this graph.
Remember the scrollbars from when I purchased a vehicle? Yeah, let’s use them. Let’s also use another cool feature, “Name Manager“. You can find the Name Manager within the Formula Tab within Excel 2007- it also exists in Excel 2003.
Quick explanation for Excel Names. For many financial related spreadsheets, the concept of interest rate is used extensively. Lets say we designate cell B1 as our interest rate. To make our spreadsheet formulas easier to read, let’s Name cell B1, iR. To do this we have two main options, either go into the Name Manager and create the variable iR (=B1). Or simply, edit the cell name next to the formula bar.
Now referring to interest rate is easy, instead of =B1, we can utilize =iR. The main advantage of this approach is that it is cleaner and easier to understand. Names can also be dynamic. As you can see, I have defined three complex names with complex formulas.
Names can also be referenced as series within Graphs. Once we Select Data > Edit Series, we are able to refer to the Names previously defined.
So here is ultimately how the Excel spreadsheet works.
- Scrollbars are created which drive our starting date and zoom features.
- Names are created which create arrays based on the scrollbar values.
- Graph Series refer to Names.
- Graphs will update based on real time changes in the scrollbar!
So with that, let’s analyze a one year return. I see a sea of red. Ouch.
Please, let’s hope for an Economic recovery soon!