I was reading my boss’s blog post yesterday about the CNN Money charts that include a red or green stripe to indicate if the market has gone up or down over each monthly period. I wanted to recreate the graph, but knew Silverlight would be a time-consuming process, and would require quite a bit of work to customize for individual projects, so I opted for Excel (PowerPivot, to be exact) which we can easily replicate going forward (and yes, I know Excel doesn’t seem like the most likely candidate for this off the bat). Let me explain:
1. Load data into PowerPivot – all you need at this point are dates and figures
2. Create columns for Start of Month and End of Month Prices (see below):
3. Next, you’ll need to pull data that will show red or green in the graph. To do this, create a column that will return 1 if Start of Month is greater than End of Month, and a column that does the opposite if less than or equal to (see below):
4. The final column you need is one that shows the Month, rather than Date, if desired (use DAX formulas for FORMAT)
5. Now, create a new PowerPivot line graph, and choose Date and Month as Axis fields, and Stock Prices (or sales figures, etc), Red and Green in the Values field (see below):
6. Your graph won’t look like this quite yet. First, you need to change Red and Green to bar graphs, and put them on the secondary axis (or primary, depending on where you want the Stock Prices to show). Widen the bar graphs so they connect, and manually change the secondary axis to around 0-30 (this will size the stripes to your liking).
7. Change the colors and fill settings to your liking. In this case, I make the blue fill 50% transparent to get the grid lines to show through, red and green for the stripes (from Excel’s standard colors), and I used a box to cover the ugly mess of dates (set to white fill with no border).
I know these steps are overly simplified, but with some background with PowerPivot, you should be able to add a new tool to your Excel toolbox in no time! Feel free to contact me with questions any time.