In Part 1 of this discussion about automated cross correlations, I explained how to set up the cross-correlation Data Table shown below, in columns J and K.
Now it’s time to set up the actual report that’s shown in the range A3:H17.
Set Up the Report Heading
Cell A3 above contains the text of the report title shown; so simply enter it.
The text to the right of the report title is returned by this formula for the cell shown:
H3: =”Shift = “&Shift&”, Correlation= “&TEXT($K$6,”#.00”)
Right-align cell H3 to position the text that the formula returns.
Whenever you change the Shift value, cell K6, which is in the first row of the Data Table, calculates the correlation for that new Shift value…without changing the results in the Data Table.
Therefore, the formula in cell H3 can easily display both the Shift value and its resulting correlation value in the title area.
The formula for the subtitle tells the reader what the chart displays. Enter it in the cell shown here:
A4: =IF(Shift=0,”Data 1 and Data 2 have the same time periods.”,”Data 1 is compared to a Data2 that has been shifted “&IF(Shift<0,”back”,”ahead”)&” by “&ABS(Shift)&” months.”)
Set Up the Chart
To create the chart, begin by charting the raw data. That is, first select the range B4:D25 in the Data worksheet, shown at the right above. Then choose Insert, Charts, Line, Line…which generates an ugly chart that looks something like this:
To begin this chart’s transformation, cut and paste the chart to an area below the title area in your Report page.
To move your legend to the top of the chart, right-click the legend; choose Format Legend; and then, in the Format Options tab, choose Top; and then choose Close.
Now it’s time to edit your SERIES formulas.
To begin, save your workbook as CrossCorr.xlsx, which will make the following instructions easier to follow. Then, when you’re done, you can name your workbook anything you want.
Now select the blue line in the chart and replace its SERIES formula in the formula bar with this formula:
=SERIES(Data!$C$4,CrossCorr.xlsx!g.DateText1,CrossCorr.xlsx!g.Data1,1)
Then select the red line and replace its SERIES formula with:
=SERIES(Data!$D$4,CrossCorr.xlsx!g.DateText2,CrossCorr.xlsx!g.Data2,2)
Your chart now should look something like this:
Select the X axis and press Ctrl+1 to launch its format dialog or side panel. In the Axis Options tab, set the Interval Between Labels to 2 units, and position the axis On Tick Marks.
Select the red line and press Ctrl+1, to launch the Format Dialog or Side Panel. In the Series Options tab, specify the Secondary Axis. After you do so, Excel displays axis values for the red line at the right side of your plot area.
With any item in your chart selected, choose Chart Tools, Axes, Axes, Secondary Horizontal Axis, Show Left to Right Axis. After you do so, Excel will show dates vertically aligned at the top of your plot area. To align them correctly, select the axis, press Ctrl+1 to launch the Format Axis dialog or side panel; in the Alignment tab, find the Custom Angle setting. Click its up-arrow once and then click its down-arrow once, which will set the custom angle to zero degrees.
Then, in the Axis Options tab, position the axis On Tick Marks. After you do so, your chart should look something like this:
Now we need to set up the colors to label the axes.
Select the blue line and press Ctrl+1 to launch its format dialog or side panel. In the Line Color tab, choose Solid Line. And then specify the color of Dark Blue. Click on the red line, choose Solid Line, and then specify the color of Dark Red.
Select the left vertical axis, and then in the Home, Font group, set a font color of Dark Blue. And press Ctrl+b to assign a bold font. And then do the same for the bottom horizontal axis labels.
Now repeat these steps for the top and right horizontal axis labels, but assign a Dark Red font rather than Dark Blue.
To remove the border around the chart, click on the border, press Ctrl+1 to launch the Format Chart Area dialog or side panel. In the Border Color tab, choose No Line.
Finally, move and resize your chart so that it’s in position below your report titles. When you do so, it should look something like this:
Set Up the Correlation Table
The table that begins in column F displays the data in the Data Table in a format that’s easier to read.
To begin, enter these formulas in the cells shown…
F7: =J7
G7: =K7
H7: =K7
…and then copy these formulas down their columns.
To format column F in the table, assign a bold font to the Shift values.
To format column G with its data bars, select the range G7:G17. Choose Home, Styles, Conditional Formatting, Data Bars, Solid Fill, More Rules…which launches the New Formatting Rule dialog. Under Bar Appearance, choose Negative Value and Axis.
In the Negative Value and Axis Settings dialog, for the Negative Bar Fill Color setting, choose a medium dark gray fill color. Then choose OK, which returns you to the the New Formatting Rule dialog.
In the Bar Appearance section, choose the same medium dark gray color for the fill color. Then check the Show Bar Only option, and then choose OK.
To format column H in the table, select the range H7:H17, press Ctrl+1 to launch the Format Cells dialog. In the Number tab, choose Number category. Specify two decimal places. Then choose OK.
To label the columns as shown, enter ShiftVal in cell F6 and Correl in cell G6. To center “Correl” above columns G and H do NOT merge cells G6 and H6. Instead, select the range G6:H6, then press Ctrl+1 to launch the Format Cells dialog. In the Alignment tab, in the Horizontal dropdown list box, choose Center Across Selection. Then choose OK.
Finally, apply the border, fill, font colors, and bold settings shown in the figure above.
