Gas Prices – Interactive Time Series
by Jon Peltier
Tuesday, August 12th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In Watch the Rise of Gasoline Retail Prices, 1993 – 2008, Nathan shows an animated bar chart that traces the price of gasoline over the past 15 years. A number of readers responded with links to assorted time series.
I downloaded Nathan’s data workbook and duplicated Nathan’s animated bar chart in Excel in Gas Prices – Animated Bar Chart for Excel, and while the animation was captivating, the viewer loses track of the actual time period of the data at the point of the animation. I added a small inset chart in Gas Prices – Animated Bar Chart for Excel 2 to show progreess along the entire time series, but this wasn’t totally satisfactory.
I went to work creating an interactive time series chart in Excel. The basic chart is a line chart showing all the data; all series are in a light color, and one particular series, selected in a listbox, is highlighted in a bright color. This technique was described in Easier Interactive Multiple Line Chart and in Interactive Parallel Coordinates Chart.
The top section of the worksheet is shown below. I inserted a couple columns to accommodate the highlighted series. Cell C1 is named ColIndex, and its value is used in formulas in column A to determine which column between D and L is reflected in column A. The formula in cell A4, for example, is
=INDEX(D4:L4,ColIndex)
The chart is shown below. The listbox is linked to the ColIndex cell, so changing the listbox changes the values in column A of the data sheet.
All series have been plotted in the line chart, using the dates in column C as the X values, and the prices in columns D through L for the Y values. All of these series are colored light gray. The prices in column A are added to the chart as a new series, and this series is given a bright color so it stands out. The last point is given a data label, using the Series Name option.
To try out the animated chart, download this zip file. It is unlocked and unprotected, so you can see how it is constructed and how it works.
Related Posts:
- Gas Prices – Animated Bar Chart for Excel
- Gas Prices – Animated Bar Chart for Excel 2
- Gapminder for Excel
- Ballistics Animation
- Rolling Wheel Animation
- Easier Interactive Multiple Line Chart
- Interactive Multiple Line Chart
- Animated Dashboards?
- Interactive Charts with Checkboxes and VBA
- Re: Abortion Ratios 1980-2003
Posted: Tuesday, August 12th, 2008 under Dynamic Charts.
Comments: 11
Comments
Comment from Steve
Time: Tuesday, August 12, 2008, 2:51 pm
In trying to modify this chart to accommodate different data (and on a smaller scale) I am have trouble getting the ListBox to change data. I am thinking you created this in Excel 2007 and I am using 2003? I can’t even select the listbox but tried adding another one. Once I did that, I couldn’t save my new workbook.
Perhaps I am out of luck because of the version difference?
Comment from Jon Peltier
Time: Tuesday, August 12, 2008, 4:50 pm
Steve -
I’m using Excel 2003. The listbox is from the Controls Toolbox, so it’s an ActiveX control, not a Forms toolbar control. On the Controls Toolbox or Visual Basic toolbar, click the button that looks like a draftsman’s pencil and triangle. Then right click on the control and choose Properties. Enter a cell address or range name for the LinkedCell and ListFillRange properties.
Comment from Steve
Time: Tuesday, August 12, 2008, 6:19 pm
Thanks for your help! The site is very helpful and reworking some of the graphs to fit my own test data helps stretch my knowledge of Excel. Much appreciated.
Comment from Steve
Time: Tuesday, August 12, 2008, 6:35 pm
Noticed though that I am getting an odd result. On the 2nd sheet in cell C1 where the Match formula is, that changes to a value whenever I click on another selection in the Listbox. Strange–it completely overwrites the formula with the value. I have used a Listbox from the Forms toolbar, so wonder if that has something to do with it. And I can’t figure out what the range RowIndex is for–yet.
Still troubleshooting myself, but having the formula over-write is weird and my value in cell A1 on that same sheet is a number, and not the text label that it should be.
Comment from Jon Peltier
Time: Tuesday, August 12, 2008, 6:41 pm
If you are using a forms listbox, its linked cell contains the index of the selected item. A controls listbox puts the selected value itself (the text label) into its linked cell. My controls listbox linked to cell A1, and C1 populates with a Match formula to get the index. Your forms listbox should link to cell C1, and A1 should have an Index formula to put the applicable label into cell A1.
Comment from db
Time: Wednesday, August 13, 2008, 1:23 am
Jon, it looks nice, but it is a little hard to make out the extent of the differences because of the scale.
Sometimes I find it is better to chart differences, ie show values for the chosen region less the national average (or as a % of it). Then you can see more detail, and you can fit all the regions quite easily on one chart.
I would also use a running average over several periods to smooth the line out a bit, because it is trends that matter, and the weekly data is noisy.
Comment from Steve
Time: Wednesday, August 13, 2008, 4:44 pm
Thanks again for the help Jon. The light finally came on and I was able to get it to work. I have looked at the Flowing Data link and saw the static chart on oil prices and thought it would be “neat” to try and make this chart a dual axis with the secondary axis charting the price of a barrel, idea being to track the gas price to the oil price. When I tried to convert the chart to a dual axis, everything went kinda crazy. The chart had a legend and some of the series was in there twice. I did not change of the ranges to include Oil as I wanted the interactive capacity to stay the same for the gas part. Maybe that’s where my thinking goes wrong.
Still, its a good learning exercise
Comment from wynand
Time: Tuesday, August 26, 2008, 1:50 am
Jon, sorry for still hounding you, but I still cannot figure out the animation of your own downloadable workbook ‘Gas Price Line’. Maybe it is my version of Excel (2003). Could it be the scales or data itself? I could not find any code or settings that provides this effect.
The animation I’m referring to occurs when I click in the chart or in the adjacent cells next to the cells or when I alternate sheets. Almost like refreshing the chart.
I have tried to duplicate this with my own data and had no luck.
Comment from Jon Peltier
Time: Tuesday, August 26, 2008, 6:53 am
Wynand -
The “Gas Prices Line” workbook has no animation, just some dynamic formulas that change when you select a different region in the listbox. I’m not sure from your description what is happening.
Comment from wynand
Time: Tuesday, August 26, 2008, 7:07 am
must be some version or settings issue, allthough I’ve neverseen this with any other chart before. Have you noticed what I’m describing or is it just my workbook displaying this?
Comment from Jon Peltier
Time: Tuesday, August 26, 2008, 9:26 am
Note: Wynand has emailed me the workbook, and I found nothing unusual. The best I could do was scratch my beard and say, in my most authoritarian tone, “Excel is like that sometimes.”
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.