Dynamic Charts

Ballistics Animation

Sunday, August 24, 2008 by Jon Peltier 7 Comments

Sunday, August 24, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

I’ve done a handful of animations lately:

This example shows the position and path of a moving projectile which is launched at a given velocity and angle. The math is high school physics, with a little calculus and trig thrown in. If you want to see that, download the workbook (link below), or consult a textbook or a smart kid.

The workbook has a data entry area and my usual animation controls. When the angle and velocity are entered, a results table shows how long the projectile will be in flight, how long it will take to get to maximum altitude, and what altitude and distance the projectile will reach. Some assumptions were made to simplify the model, as in all physics problems. The ground is flat at sea level, and the effect of air resistance is neglected.

Ballistics Animation Demo

Set the angle to 45° and the velocity to 100 m/s, and start the animation.

Ballistics Animation Demo

Here is the end of the animation for 30° and 7 m/s.

Ballistics Animation Demo

And this one is for 75° and 100 m/s

Ballistics Animation Demo

The workbook containing this model can be downloaded here: Ballistics.zip.

Posted: Sunday, August 24th, 2008 under Dynamic Charts.
Tags: Animated Charts, ballistics, Dynamic Charts, Educational, Interactive Charts, Physics, VBA.
Comments: 7

Microsoft MVP Logo

Presidential Demographics

Sunday, August 24, 2008 by Jon Peltier 4 Comments

Sunday, August 24, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

In Presidential Demographics, Part II, Robert Kosara has posted a very nice interactive infographic that allows you to answer a host of questions about US presidents:

Would McCain be the oldest US President? Would Obama be the youngest? Who was the youngest president? Were presidents younger in the past or older? What is the highest number of years a former president lived after leaving office? Who served the longest? Whose term was the shortest?

The chart lets you view presidents lifetimes and terms in office on a regular timeline, aligned by birth, or aligned by start of time in office. When you mouse over a bar, a pop-up displays the president’s name, date of birth and death, and dates in office. Here are a few screenshots.

EagerEyes Presidential Demographics Timeline

EagerEyes Presidential Demographics By Birth

EagerEyes Presidential Demographics By Ascension

I found this graphic very interesting, and I even learned a couple things. For example, I knew JFK was the youngest president ever to take office, until this chart showed me that Teddy Roosevelt actually held this honor. If I get ambitious, I may try to replicate this graphic in Excel.

Posted: Sunday, August 24th, 2008 under Dynamic Charts.
Tags: Animated Charts, EagerEyes.
Comments: 4

Microsoft MVP Logo

Rolling Wheel Animation

Sunday, August 17, 2008 by Jon Peltier 11 Comments

Sunday, August 17, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A college science professor named Roger Blickensderfer wrote to me about an animation he was working on. He wanted to trace the path followed by a point on the rim of a rolling wheel. Roger had cobbled something together, and I fiddled with it, and it kind of worked. But I decided I wanted it to do a bit more. I started with Roger’s basic idea and added my flexible animation from the Gas Prices – Animated Bar Chart for Excel example. I added a second point, which can be placed at a different distance from the axis of the wheel, and I added a checkbox to show or hide the curves traced by these two points.

I built an animation which can be extracted from this downloadable zip file. The top of the worksheet has the playback controls and other inputs to the left and the chart to the right. The first example uses a wheel radius of 10 and a second point at a radius of 0 (the axis of the wheel)

Rolling Wheel Setup

Press the button to start the animation. The red line stays at the same height, while the blue line traces a curve called a cycloid.

Rolling Wheel Setup

Here are the traces when the second point is at a radius of 5 (midradius).

Rolling Wheel Setup

Here are the traces when the second point is at a radius of 15 (outside the radius of the wheel rim). This is similar to the case of a train wheel, which has a flange to keep the tain on the track. The outer surface of the flange actually moves backwards at the bottom of its travel.

Rolling Wheel Setup

In this example, the second point has a radius of -10, so it appears on the rim opposite the first point.

Rolling Wheel Setup

I found another explanation of this in Rolling: the physics of wheels, on the web site of the School of Physics at The University of New South Wales. There is a large collection of similar animations on their Physclips page.

Posted: Sunday, August 17th, 2008 under Dynamic Charts.
Tags: Animated Charts, Dynamic Charts, Educational, Science & Engineering, VBA.
Comments: 11

Microsoft MVP Logo

Gas Prices – Interactive Time Series

Tuesday, August 12, 2008 by Jon Peltier 12 Comments

Tuesday, August 12, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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.

Posted: Tuesday, August 12th, 2008 under Dynamic Charts.
Tags: Animated Charts, Dynamic Charts, FlowingData, Interactive Charts.
Comments: 12

Microsoft MVP Logo

Gapminder for Excel

Monday, August 11, 2008 by Jon Peltier 19 Comments

We all have been awed and amazed by the Hans Rosling talk Debunking third-world myths with the best stats you’ve ever seen.

The software used by Professor Rosling is Gapminder. There are demos and links to numerous similar presentations on their web site.

I’ve made numerous animated charts in Excel, including Gas Prices – Animated Bar Chart for Excel and Gas Prices – Animated Bar Chart for Excel 2. I’ve been meaning to try one of these Gapminder-style animated bubble charts, but I’d never taken the time. However, I received an email from Wiebe van der Waals, who had put together a prototype Gapminder chart in Excel.

Here is Wiebe’s simple data, followed by his chart, which I’ve adjusted a bit.

Gapminder-in-Excel Data

Gapminder-in-Excel Chart

Move a scroll bar to display the point for a particular year. Click a Play button to show each year in sequence.

Wiebe’s animation was very simple, jumping from one year to the next in a very irregular fashion, but it had the right idea. To smooth the animation, I introduced an interpolation routine so the transition from year to year took a series of steps in between individual data points. Here is the current state of the animation program.

Gapminder-in-Excel VBA Code

There is still a lot of room to improve this simple charting animation. There is already a scrollbar and a Play button. It would be easy to include additional controls as in my Gas Price animation, such as Pause, Reverse, Go To Start, Go To Finish. Other adjustments would include the ability to change the number of steps in the interpolation (smoothing) algorithm and to change the playback rate, preferably on the fly.

Download the Gapminder Type Bubble Chart workbook, try it out, and give me your comments. The code is unprotected, so you can see how it works. This is a very cool addition to Excel’s arsenal.

Update 9 December 2008

In response to Eric’s comment, I’ve put together a version of this file in which the moving bubble leaves a “breadcrumb” corresponding to the data point for each year. This new workbook is described in Gapminder For Excel II.

Gapminder for Excel chart with trailing breadcrumbs

Posted: Monday, August 11th, 2008 under Dynamic Charts.
Tags: Animated Charts, Gapminder, Hans Rosling, Interactive Charts, VBA.
Comments: 19

Microsoft MVP Logo