Tuesday, August 13, 2013

I have to look at the spreadsheet (2)

Picking up where we left off yesterday...

I wanted to use a Zoho spreadsheet, so you could click on cells in the sheet and look at the formula bar to see what the calculations are. But the Zoho sheet makes the Blogger screen jump down to the spreadsheet, and as a blogger I won't stand for that.

My next choice was to use Google Drive. But the series values are quarterly, and that sometimes means there are too many pieces of information for the Google Drive spreadsheet to handle. The graphs don't come out right.

So Excel it is. That means I can show you the graphs, and I can show you images of cell calculations if it comes to that, but if you want the live-action of a spreadsheet, you'll have to download the Excel file and get into it yourself.

Or you can read the story I tell here and take my word for it.

I don't have Excel on the computer where I do my blogging. I've been using Open Office Calc, which can read and write Excel files. But Open Office messes up the x-axis labeling on Excel graphs, and I prefer not to manipulate Excel-created graphs on this computer because of it.

And I have some nice Visual Basic routines I've been developing to format my Excel graphs, to standardize the size and appearance of those graphs. But those routines don't work in Open Office. I don't know how to make them work in Open Office.

So again, Excel it is.

Working in Excel on the old computer, I tried to duplicate Lars Christensen's graph from memory. From the FRED data, using what I understood of Christensen's calcs from looking at it on the blogging computer.

In Excel, my first step was to look at the FRED data. It should look the same as the FRED graph in yesterday's post:

Graph #1

If it didn't, I would want to check my work for errors. I was happy with it. So the next step was to duplicate Lars Christensen's graph:

Graph #2: Lars Christensen's Graph

Graph #3: My Version of Christensen's Graph
Oh, I should make my horizontal gridlines faint.

The two graphs are a pretty good match. Christensen gets more vertical than I do -- the space between his horizontal gridlines is near twice what mine is. Mine got squeezed between the title and legend, above, and the x-axis labels, below the plot area. No amount of multiplying by the series standard deviation can make up for that. But you can see, on both graphs, the one line is pretty well centered upon the other. And the overall up-and-down offsetting is about the same, red versus blue, on both graphs.

But I'm leaving things out of the story. The first time I tried to duplicate Christensen's graph I was way off. For some reason I thought I had to "standardize" the NGDP values the same way Christensen was standardizing the other values. So all my lines were centered on the zero level. And that didn't look like the graph I was trying to duplicate. So I had to go over it again before I got it right.

I had to do it on the old computer. I had to look at Christensen's file in Excel and see which numbers he used in the graph, before I got it right. He takes his "index" value -- "standardized" S&P 500 less "standardized" Dollar Index -- and to it adds the average value of the NGDP series. This brings the index numbers up, centering them on the NGDP numbers. It gets the numbers away from the zero level.

There are additional adjustments: Christensen subtracts 1.5 from the index-plus-NGDP-average, for even better vertical alignment of the two series. Then he multiplies this centered number by the NGDP standard deviation and divides by the "index" standard deviation, to scale the index numbers to about the same size as the NGDP numbers.

He ends up with an index he calls the NGDP Market Indicator, which has about the same up-and-down range of values as NGDP and is centered on NGDP, for greatly improved visual comparison.

Okay, I get it. Lars is centering and scaling his index numbers, so that he may better see similarities and differences between the index and the NGDP numbers. He sees the size and the location of the up-and-down pattern as separate from the pattern itself.

And you know what? I did have to look at the spreadsheet!

No comments: