The New Arthurian Economics: Zoho

Showing posts with label Zoho. Show all posts

Sunday, September 14, 2014

Hoodwinked by the indexing

Back in February I came across this graph at The Current Moment:

Graph #1

Also this one, which they called "Doug Henwood's Graph":

Graph #2

Both graphs show productivity. The one graph compares productivity to compensation, the other compares it to wages. Both wages and compensation lag productivity. But compensation increases a lot more than wages, because compensation (as the source site explains) is wages plus benefits.

I find it useful when people provide background information like that. But that's not what caught my eye. It's the difference in separation points that caught my eye: On Graph #1 the "wages" line breaks away from the productivity line suddenly, just around 1974. On Graph #2 the "compensation" line breaks away from the productivity line gradually, but also much earlier -- possibly as early as 1960. This is the kind of thing that fascinates me.

I did notice that the first graph is indexed "relative to 1970" while the second is "rebased to 1960". In other words, on Graph #1 the lines have the same value at 1970 and on Graph #2 they have the same value at 1960. (The other values are adjusted accordingly.) This choice of different "base" years changes the date that the lines on the graph appear to separate, and affects the impression that the graphs give us. I think we are hoodwinked by the indexing.

On Graph #1 in particular, between 1955 and 1975, it's pretty clear that the orange line is going up faster than the red line. If you could take the whole red line and just move it down a little bit, you might make the two lines touch at only one point. That point would be around 1956. And if you looked at that graph, you would see real wages falling behind productivity since 1956.

You should find it disturbing: not only the decline, but also the many years of decline that are overlooked when we say that compensation fell behind productivity around 1974.

The second graph, Henwood's graph, seems to show compensation falling behind productivity since about 1960. But it doesn't show earlier data, so it leaves the door open. 1956 is not shown to be wrong.

I followed the Current Moment link back to Doug Henwood's and liked what I found there. I emailed Henwood:

Hi. I recently came upon an old page (2001) at Left Business Observer

http://www.leftbusinessobserver.com/Stats_earns.html

I'd like to use your "productivity and compensation" graph on my blog. The page says I should get permission first, so I'm asking.

All the similar graphs that I've seen focus on the separation beginning mid-1970s. Your graph shows the separation beginning much earlier. It was eye-opening for me.

"Heavens," Henwood replied. "I have much more recent versions of that - let me find one for you tomorrow." He did, too:

Graph #3

"Here's the latest," he wrote last February. "Haven't updated it since November, but it's through the third quarter of last year. 'Compensation' includes fringe benefits - since much of that is health insurance, much of the real value is eaten up by medical inflation. Direct pay is pay without fringe benefits. All are inflation-adjusted and indexed so the base year = 100."

The three lines on this graph combine the three different series shown on the first two graphs above. In addition, on Graph #3 the series are indexed to 1964. And -- don't you know it! -- the separation point this time looks to be the late 1960s, or earlier.

A little over a week ago -- it seems much longer -- I was reading a discussion at Reddit. The topic was "What might actually be holding back workers’ wages".

Somebody blamed Reaganomics. One guy, I'll call him Joe, rejected that idea: "Your idiotic blaming of Reaganomics would be dependent on Reaganomics traveling back in time a decade and starting the trend in the mid 70's," Joe said.

I love it. That's one of my themes: You can't just blame the guy you don't like, especially if the things you're blaming him for happened before his turn at bat. Reminds me of a Mike Kimel quote that Jazzbumpa has in his sidebar:

#15 Time moves in a single direction.

No doubt.

Anyway, Joe provided a link to a "productivity and real wages" graph -- the same graph from The Current Moment that I have as Graph #1 above. Small world.

I complimented Joe on the graph. But then I went off-topic, so much that Joe had to disagree with me. I pointed out that the graph is indexed "relative to 1970". I said: "If the graph was indexed relative to 1956 we would see the slowing of wages begin in the mid-1950s and slow more since the mid-1970s."

Joe replied: "That is not how the graph would change changing the index year at all. Here is one indexed to 1947. There is still a trend starting at 1970 where wages and output are decoupled."

He showed this graph:

Graph #4: http://www.econdataus.com/wagegap12.png

Yeah, the red and blue lines are comparable to those in the graphs above. And yeah, the base year is 1947 this time.

But this graph has a third line -- the purple line -- that shows the ratio of the red and blue. Nice! I was going to get around to showing the ratio.

The purple line shows how productivity ("hourly output") is changing relative to wages. And if you look at the purple line you can see it starts going up around 1956 or 1957. That means productivity started gaining on wages around 1956 or 1957. (And come to think of it, the blue line on Graph #4 looks like wages plus benefits, not just wages.)

Wages started falling behind productivity around 1956 or 1957. So if you want to blame Reaganomics... or if you want to blame Jimmy Carter or Gerald Ford or Richard Nixon or Lyndon Johnson -- or John F. Kennedy for that matter -- to do it you will have to make time go in the wrong direction. You'll be breaking Rule #15.

The failure of wages and compensation to keep up with productivity is a problem that began in the 1950s.

Yesterday, at The State of Working America I found this graph from the Economic Policy Institute:

It is similar to the graphs above. The dark blue "productivity" line goes up faster than the light blue "compensation" line since the mid-1950s. But the indexing has the two lines tangled together so you don't notice compensation falling behind until the mid-1970s.

This graph is important because it comes with this data. (Excel XLSX, 37KB) So of course I took the file, uploaded it to Zoho, and customized it for on-line use.

Please make sure the proper spreadsheet tab is selected on the spreadsheet below: At bottom-left in the spreadsheet window there is a little triangle pointing up, then there are the tabs (which you can read only part of), and then there is a left-pointing triangle and a right-pointing triangle. You want the last tab. To access it, click the right-pointing triangle. The text in the tab area scrolls so you can see "Adjustable Base Year" or maybe only "ble Base Year" (which is what I see). Anyway, click that gray "Base Year" tab. The tab will turn white and the worksheet area will change to show a tidy little graph, and above it a yellow spreadsheet cell with a date in row 1. Click the yellow cell to select it.

Change the 4-digit year value in the yellow cell to "rebase" the two lines to the year of your choice. The graph runs from 1948 to 2013. You can use any year in that range to change the base year for the graph. It shows you that Compensation started falling behind productivity right around 1956.

Spreadsheet-handling hints and other edits, 14 August 2022 by Art

Monday, June 2, 2014

Kervick's Piketty Spreadsheet

I read Tom Hickey's Dan Kervick — Naked Piketty – with a Bonus Workbook! and followed the link to Kervick's post.

I downloaded Dan Kervick's 22k Excel spreadsheet.

I went to Zoho and signed in.

I uploaded Kervick's spreadsheet, accepting the defaults.

(The process is much like uploading to Google Docs.)

I got this message: Uploaded and converted successfully

I closed the upload window and opened the file.

With the spreadsheet open, I clicked the SHARE tab.

On the submenu I clicked EMBED.

I clicked OK, accepting the defaults.

An Embed to Website/Blog window opened.

I clicked SELECT SNIPPET and then pressed CTRL-C to copy the snippet.

I pasted it into my Blogger editor, here:

It's an HTML IFRAME thingie. I happen to know that the maximum width my blog can display is about 517 pixels. So in the IFRAME thingie I changed the text width="600" to width="517".

Then I scheduled this post to appear Monday at 4AM.

That's all I had to do.

On the blog, the Zoho sheet's menu is hidden, but the formula bar is visible. So you can click on any cell, then look in the formula bar and see the formula or whatever is in the cell you clicked. This is a massively important feature.

A visitor to the blog can examine the structure of the spreadsheet, and can even make changes. But these changes affect only the copy displayed on the blog. The original spreadsheet is not changed.

If the visitor messes up his copy of the spreadsheet, refreshing the page replaces the messed-up copy with a fresh new copy of your original spreadsheet.

Sunday, February 9, 2014

Kimel's spreadsheet is a beautiful thing

Mike Kimel's spreadsheet contains a page where he pasted BEA Table 3.2. The table shows a breakdown of Federal government revenue and spending for the 1929-2010 period. Then at the far right, beyond the table, he pulls out and gathers the data on "current revenues" and "current expenses" and uses those numbers to calculate a surplus or deficit for government spending.

Where Kimel pulls out and gathers the numbers, he doesn't do it by copying and pasting. He does it by setting cells equal to values in the table. "Referencing" cells in the table, it's called.

This is kind of important. I captured part of Kimel's spreadsheet page in Zoho so you can look at it. Column C contains the BEA's breakdown of Federal receipts and expenditures. You don't have to memorize it, but you should note that the breakdown contains a lot of line items -- 46 of them, according to the numbers in Column B. In the spreadsheet, you can scroll down to see the whole list. You can press CTRL+HOME to get back to the upper left cell A1.

You can scroll to the right. To the right of Column C, I've included the first five years of the data that run from 1929 to 2010 on Kimel's sheet. The numbers in this table are numbers, not calculations.

At the top of the spreadsheet below, just down from the title "Kimel Fragment" is a white field less than an inch wide, then the letters FX in some fancy font, and then a white field about 3 inches wide. That wide white field is the "formula bar". That field displays the contents of the cell you select, and it changes as you move the cell selector.

If you move the cell selector to column D or E or F or G or H, you can see that the entries for "Current receipts" and "current tax receipts" and "Personal current taxes" and the others are actual numbers. Not calculations or formulas, I mean.

If you scroll a little more to the right you come to a blue area. (I made the background blue so I could describe it easily and you could find it easily.) Scroll a little more to the right, so you have the whole blue area on the screen.

If you move the cell selector around in the blue area, you will notice that the titles on Row 2 ("current receipts" for example) appear in the formula bar when those cells are selected. That tells you that Mike Kimel (or somebody) actually typed those words into those cells. Or maybe that they copied those words from somewhere else and pasted them into those cells.

If you move the cell selector along the left edge of the blue area, on the year values, you will notice that the year values appear in the formula bar. These numbers also were typed in, or pasted in.

That could be done differently. You could type 1929 in cell J3, and do the numbers below it just by adding 1 each time you go down a line. For example, in cell J4, where you want the value 1930, you want a value that is one more than the value in cell J3. So you could add 1 to the value in cell J3.

To do that you would select cell J4, then type an equal sign, the number 1, a plus sign, the letter J, the number 3, and then you would hit the ENTER key. (Try it if you want. You won't mess up the sheet. If you do mess it up, just refresh the page and start over. No harm done.)

When you hit ENTER the cell selector moves down a row, onto the value 1931 (which is a typed-in number). Move the cell selector up a row, onto the value 1930. Notice that in the blue area, the cell shows 1930 but in the formula bar it shows the formula that you typed in: =1+J3 .

That's why I like Zoho. It lets you use the formula bar online.

Now if you wanted to recreate the value 1931 in the next cell down, you could do it with a formula just the same way. Actually, it would be the same formula. (And now we're getting somewhere!) In cell J5 you would want the value 1 plus the value in the cell above. But you don't have to type the formula again. There's an easier way.

Move the cell selector to cell J4, where your formula is. Right-click on that cell, and select COPY from the little menu that shows up.

Select the next cell down -- cell J5 -- and press CTRL-V.

Nothing happens in the blue area. But the formula bar changes.

Do it again. Select the next cell down and look at the formula bar to see that it contains a year number. Then press CTRL-V, and the formula bar will show your formula instead.

Okay, now for the good part. Click on the next cell down, but don't release the mouse button. Instead, drag the mouse down several rows. Yes, yes, do go three or four rows off the blue area, yes!

Now press CTRL-V.

All the cells you selected get the blue background color. And, if you stayed in Column J, all the cells you selected now show year values, in sequence.

So I hope you already knew how to do that. But if you didn't, wow. You should learn. I'll help you if you want. We can do more of this spreadsheet stuff. Just not in this post.

So anyway, look at the values for "current receipts" and "current expenditures" for 1929 to 1933, in the blue area on the spreadsheet. In the blue area, there are values. But in the formula bar, there are formulas. The one for cell K3 shows =$D$3 for example.

It starts with an equal sign, just like the formula we entered to get the year values.

There are a couple dollar signs in it though. That's okay. We're not doing any more spreadsheet lessons in this post. Overlook the dollar signs in the formula. If you ignore those dollar signs, the formula looks like =D3. That means the value in cell K3 comes from cell D3. If you scrolled back over to Column D, you'd see the same value in that cell as in cell K3.

And in fact, if you changed the value in cell D3, the value in cell K3 would also change! That's pretty neat. It's a powerful feature of spreadsheets, and it's one reason people use them. I made use of that feature for this series of posts.

What I did was, I got the latest values from the BEA site, their Table 3.2 that shows current receipts and current expenditures. I added that sheet as an extra sheet in Mike Kimel's spreadsheet. And then I went into Mike Kimel's Table 3.2 and used formulas to set the "current receipts" and "current expenditures" values in Kimel's table equal to the new 2014 values.

I only had to create formulas for the 1929 values. Then I copied those formulas over to the later-year columns. Easy as that, Kimel's sheet contained the new 2014 values.

And then, because Mike Kimel did his spreadsheet the right way, using formulas that refer to to other cells, all of his numbers got updated. (I only changed "Line 1" current receipts and "Line 20" current expenditures. But those are the only numbers Kimel uses from Table 3.2. And every place he used those numbers, they got updated.)

So I made an easy change, updating Mike Kimel's data. And the prize I got for that was an updated version of his graph, as you can see below.

Graph #1: Kimel's Graph with Kimel's BEA Data

Graph #2: Kimel's Graph with Current BEA Data

Related Files

1. Mike Kimel's original Excel file, which he very generously sent upon my request: Tabarrok and Cowen 20110217 follow up 20110221.xls

2. Downloaded from BEA, the current (30 Jan 2014) version of Table 3.2 (for Federal spending and revenue): BEA download.xls

3. File 2 as a page in file 1, allowing me to revise Mike Kimel's graph by referencing the more recent values: Current BEA data.xls

4. For yesterday's graph of Real Private Spending in the 1960s, the Google Drive spreadsheet named A Look at Mike Kimel's Data. That's the original Excel file Kimel sent me, converted to Google Docs format. So I could look at his data for the 1960s.

Sunday, January 26, 2014

Checking Maynard's arithmetic

In the previous post I quoted Keynes on the growth of capital. He said $1 grew into $100,000 in 350 years. (He actually used Pound signs, but I'm lazy.)

He said it was a growth rate of about 3.25 percent.

He also said 250 years, but that was a mistake. After I got confidence that it was 350 years and I understood it right, I looked at the numbers:

In an Open Office spreadsheet
I put the value 1 in cell A1.
I put the value 1.0325 in cell B1.
I put the formula =A1*$B$1 in cell A2.
I copied cell A2 to the range of cells starting with A3 and ending with A350.

I know, it's only 349 years...

I had to increase the growth rate value in cell B1.

For a growth rate multiplier of 1.0335 I get 98,709.25 in cell A350, just a bit under the 100,000 target value.

For a growth rate multiplier of 1.0336 I get 102,099.29 in cell A350, just a bit over the target value.

I get a growth rate between 3.35% and 3.36%, close enough to the given number 3¼.

I recreated the thing in a Zoho spreadsheet so I could post it here. You can fiddle with it, change any numbers you want. If you mess it up too much, just refresh the page, and Zoho will reload my original sheet for you.

To look at what I'm trying to show you, just tweak the 1.0325 number in the yellow cell, cell B1. Change that number to 1.0335 and you should get 98,709.25 like I got in the OpenOffice sheet. Change it to 1.0336 and you should get 102,099 and change. So you know the growth rate number is somewhere between 1.0335 and 1.0336.

The other yellow cell is for checking what I quoted from the Japanese translation, in a comment on the previous post.

The guy said "250 square of 1.0325" is "about three thousand". I got 2968 and change: about 3000.

Then he increased the start-value from 1.0 to 40 thousand pounds, multiplied his "about 3000" by 40,000, and realized that the answer (120 with six zeroes after it) was nowhere near Keynes's 4 billion number.

Here's the Zoho sheet:

The thing I love most about Zoho is that it includes the formula bar. So you can click on a cell that you want to know about, then look at the formula bar and see the formula in that cell. Usually you can't do that online. Zoho is great!

Friday, April 26, 2013

A second look

Marcus takes a second look at government spending relative to GDP, and its relation to economic growth.

It may be easier to see the circularity in Marcus's analysis if I say he is looking at government spending relative to GDP, and its relation to changes in GDP. For if we're looking at changes in GDP, those changes will have to have an impact on the ratio called "government spending relative to GDP".

Marcus has an interesting approach. He considers a long period (1960-2007) and looks at government expenditure as a percent of GDP at the start and end of the period. He then looks at RGDP growth near the start and near the end of the period.

He looks at data for a dozen countries. I will look at just one of them. The US was at the top of his list of twelve, as this clip shows:

Part Screen Capture of Marcus's Table

I took Marcus's Gov. Expenditures (GX) numbers along with start- and end-year RGDP values from FRED, put them into a Zoho spreadsheet, and calculated US government expenditures in inflation-adjusted dollars.

// UPDATE: After a recent Zoho update, their spreadsheet now "takes the focus" when the page loads, and the screen jumps to make the Zoho sheet visible on-screen. Took me a few days to figure out a fix for this. I put the Zoho sheet into a "spoiler" that remains hidden until you click this text to make the Zoho sheet visible... (I'm disabling the spoiler.)

~~//UPDATE: But now it never loads... I'm about to give up on Zoho.~~

//UPDATE 7 May 2013: IT WORKS!!!!! Thank you, Zoho Support.

Then I took Marcus's Real GDP Growth numbers and put them on row 8. And I said to myself: I wonder how things would have looked if RGDP growth stayed at the high rate of the early years, 4.3% annual, rather than dropping off so much...

So I took the start-year RGDP value and Marcus's annual growth rate and put them into a compound-interest formula from Chron to see what the end-year value of RGDP would be in 2007, after 47 years. (Cell E10.)

You can check my math. I think it's good.

Anyway, after 47 years of 4.3% growth RGDP would have been 20 460 point 6. With the less impressive growth that we actually had, RGDP was 13 206 point 4. So, 20 versus 13, a pretty big difference.

So now we can take the government expenditure number for 2007 from cell C5 and look at it as a percent of the impressive RGDP number, and compare that to Marcus's GX/GDP number in cell C3.

Okay. Back in 1960 GX was 28.4% of GDP. Then, given the suck-ass GDP growth that we had for the last 40 years, in 2007 GX was way up high, at 36.7% of GDP. But if growth had continued at the early-years rate, US government expenditures would have fallen from 28.4% to 23.7% of GDP.

And I'm not fiddling with the government spending numbers. Just looking at what-if growth. So what I'm saying is that US government expenditure growth slowed since 1960, just not as much as GDP growth slowed.

How we get from that, to the idea that increasing government expenditure is the cause of slow GDP growth, the logic escapes me.

Saturday, April 20, 2013

Zoho it is, then

Konczal's R&R screen clip, again:

I put the country names and the debt/GDP values from Konczal's clip into a Zoho spreadsheet so we can play with it online. You can edit the zoho and see the results (and even save it as an Excel file if you want, apparently). You won't mess up my Zoho sheet. If you refresh the page, all your changes go away.

Look in particular at the values on Zoho row 29. These are the R&R error cells. Click on Cell B29, then look up at the top line of the Zoho to see what the formula is. It says:
=AVERAGE(B4:B18)
Here's what I want you to do: With Cell B29 selected, press DELETE to erase the formula. Now, type the EQUAL sign and the word AVERAGE and then open parenthesis (that's SHIFT 9 on my keyboard). Next, click Cell B4 and hold the mouse button down, then drag down to row B23. Release the mouse button. Press ENTER.

Zoho closes the parentheses for you, and calculates the average value for the cells you selected. The cell value changes.

On my computer when I do the next step the screen jumps. Just scroll back so you can see everything again.

Select Cell B29 again, and press CTRL C to copy the formula you entered.
(Scroll the screen if necessary, but don't click or type anything.)

Select cells C29 through E29 and press CTRL V to paste your formula into those cells. The values change because of your new formula.

You did it! You fixed R&R's error!

The New Arthurian Economics

Debt Accumulation

Google Translate

Blog Archive

Links

Labels