Stock charts come in various shapes. There are candlestick stock charts, line and bar stock charts, “Kagi” stock charts, moving average stock charts and everything in between. For a financial service provider how to present financial information pertaining to a specific stock can a critical question. On one hand, if you give too many options, you loose out on user comfort and increase API complexity. On the other, give too few options and you end up losing more demanding customers. Interesting to note is that both Yahoo and Google tend to use the same type of stock chart displays though personally I tend to use the former.
This stock chart has the ability to scroll across historical share price data and yet allows the user to zoom into any specific portion that may need to be studied with greater detail. This is something that would’ve taken me more than a few page refreshes to achieve using a conventional stock chart (something that is still used by a number of financial service providers even today). And that got me thinking, is there a way to make a stock chart in excel that has both these abilities. The good news is that I was able to get pretty close to creating a similar looking stock chart with scroll and zoom.
The stock chart above that has two controls – the first one allows to you move across a timeline and view changes in share price and volume over the complete time horizon. The second control allows you to control the amount of data you see in the chart. You can move the slider and compress or expand the time duration for which data is shown in a single frame. The obvious advantage of using this type of a chart is that rather than having a large number of smaller charts to study the stock’s behavior over a period of time, you can use a single chart to view the entire available data in one go and narrow down into specific portions the stock chart that warrant closer scrutiny.
To create a stock chart in Excel with scroll and zoom functionality, follow these steps:
1. Place the data required for the stock chart (stock price and volume in our case) in an excel spreadsheet. Ensure that the data is clean and free from errors. While placing the data in the excel spreadsheet, ensure that the successive data elements are placed in adjacent columns. Although it is not a mandatory step, it will help the data layout look neat and help debug formulae without errors.
2. Create another blank range which is of the same width and height as the original data set (in point 1).
3. Now in the first cell of the new range (range2) , use the OFFSET function to offset the corresponding cell in original data set by X number of rows (hint use =OFFET(original_cell, X_rows_to_offset, 0)). Copy this formula to all cells in the range such that each cell in range2 is offset by the same number of rows. Now, the exact number of rows to offset (represented by X) will be controlled by a particular cell. You can use any cell in the spreadsheet for that purpose such that it does not interfere with the chart data. Replace X_rows_to_offset in the above formula with the address of the cell that controls the offset. In step 9 we will get to make this cell change its value dynamically by attaching this to a scroll bar.
4. Create a chart in the spreadsheet using ‘Insert’ -> ‘Chart’ option from the menu.
5. Format the chart as per your needs (you can use bar charts, line charts, box plots or any other chart type that serves your purpose and the available data.)
6. Enable the Forms Toolbar by clicking ‘View’ -> ‘Toolbars’ -> ’Forms’ from the menu.
7. Important – Click anywhere on the chart window. From the Forms Toolbar, select the scrollbar icon. Now draw a neat little scrollbar within the chart area.
8. Double click the scrollbar and in the ‘Controls’ tab choose the ‘Cell Link’ box. Select the cell we choose the cell that controls the offset value of the cells in range2 (refer step 3). Provide the minimum value as 1 and the maximum value as the count of the number of rows that you have in your original data set.
9. Now press Esc a couple of times. Your stock chart should be nearly ready at this point. Simple play around with the scrollbar, drag it around and you will see that the chart scrolls along the x-axis. If nothing happens, check through all the previous points especially 3 and 8.
10. Once you have the scroll functionality working on the stock chart, its time to add the functionality to stretch the graph. The ability to stretch or collapse a time frame allows the user to narrow down or broaden the amount of historical stock price data that he/she gets to see at one go. It can be useful to quickly identify points of interest and then analyze them in greater detail.
11. To add the stretch-collapse functionality to the stock chart, repeat step 7 to add another scrollbar within the chart.
12. For the second scrollbar, repeat step 9. Select the linked cell as a new cell adjacent to the one linked to the first scrollbar. The minimum and maximum values should be somewhere between 7 and the number of days for which you’d like to see the data together.
13. Now comes the tricky part. To ensure that our data points in the stock chart expand/contract as per the user needs, we will have to delete the earlier data series and create a dynamic named range that stretches and contracts dynamically.
14. Create a new named range using “insert’ -> ‘Name’ -> ‘Define’ from the menu. In the Define Name box, enter the following formula (=OFFET(original_cell, 0, 0, Y_rows_to_offset, 0)). This is a slightly different variation of the OFFSET function which lets you expand or contract a range by a certain amount of rows or columns. (See named range for more on this topic). You will have to create as many of these named ranges as the number of data series you want to show on your chart (plus one for the label). The ‘original_cell’ here is the first cell of each of the corresponding data series (first cell of each column of range2) in the worksheet.
15. Right click on the stock chart and choose ‘Source data’. For each of the data series, edit the source data by replacing the source range reference with the name of the corresponding dynamic named ranges that you created in step 14. (Caution – If the named range does not result in a valid range, this will cause problems)
16. Click ok. Now play around with the second scrollbar and you should see the stock chart stretch or expand as you drag it with the mouse. If it does not, go through steps 11 to 15 again.
If you followed all these steps closely, you would have observed that we did not use VBA code anywhere in this example although I may add, that the results was as good looking as the Google stock chart.