Category Archives: Excel
I bring fresh showers for the thirsting flowers,
From the seas and the streams;
I bear light shade for the leaves when laid
In their noonday dreams.
From my wings are shaken the dews that waken
The sweet buds every one,
When rocked to rest on their mother’s breast,
As she dances about the sun.
I wield the flail of the lashing hail,
And whiten the green plains under,
And then again I dissolve it in rain,
And laugh as I pass in thunder.
The Cloud, Percy Bysshe Shelley
As a child I spent what seemed like hours at a time watching clouds move across the sky, shifting shapes as they went. Seeing dragons, devils, ships, and castles moving and morphing across a blue canvas. I can’t be the only one. Rain clouds rolled in this morning and I found myself watching as a few low-lying, dark gray ones trundled along beneath the overcast sky.
Lower clouds appear to be moving faster than higher ones, but this is an illusion. In reality, wind speed increases with altitude. But when a low cloud bears down in its dark and shadow and immensity, it’s nearly impossible not to tremble at one’s own insignificance.
So, what creates the illusion of faster movement? The answer lies in the changing angle of an observer’s eye as it tracks a cloud. The observation angle changes faster when a cloud moves faster or when it’s closer to the observer. Closer can mean altitude–the cloud is lower in the sky, or it can mean distance over the ground–the cloud is closer to being directly above the observer.
So how much difference does it make?
Start with the sky, and a cloud, and it’s a sunny day, and there’s a guy standing on the ground looking at the cloud. The cloud’s altitude is a, and the distance over ground is d. Take a line straight into the sky and another that goes from the guy’s eyes to the cloud. Those two lines make an angle, θ. A breeze blows on the cloud, pushing it horizontally with velocity H, and vertically with velocity V.
So now for the nerdy stuff. When the cloud’s to the right of the dude, d and θ are positive, and to the left they’re negative. a is always positive. H is positive when going right and negative when going left. V is positive when the cloud moves up and negative when it moves down. The tangent of θ is d divided by a, and can be calculated if their lengths are known.
The total change of the angle θ with time is found by adding the change in angle due to horizontal movement to the change due to vertical movement:
Gad, that’s ugly to work with. It basically says when the cloud flies left, the angle changes in the negative direction. When the cloud is to the right of the observer, the angle changes in the positive direction when the cloud moves down, and in the negative direction when it moves up. And when the cloud is to the left, vertical movement causes changes in the opposite direction. How does it look when calculated?
I started with altitude and horizontal distances of 200 feet, and since they’re equal, the angle is 45 degrees. The cloud flies by at 10 feet per second, and the observers eyes track it across the sky. Here’s what the angle, θ looks like over time. It starts out at postive 45 degrees, reaches zero when the cloud is directly overhead, and goes negative as it flies to the right of our guy on the ground.
So what happens if the cloud is now 20 feet off the ground instead of 200, and still whizzing by at 10 feet per second? Well, at first it’s just a cloud on the horizon, getting bigger and bigger, and our guy’s head doesn’t even have to move. It takes 15 seconds for the angle of observation to go from 85 to 70 degrees. Then the cloud flies over in a tear, going to an angle of -70 degrees in only 13 seconds, before shrinking into the horizon.
This reminds me of something:
“How did you go bankrupt,” Bill asked.
“Two ways,” Mike said. “Gradually and then suddenly.”
—Ernest Hemingway, The Sun Also Rises
A shot of the excel sheet and the formulas are below. Happy cloud watching.
B3 = A2 + E2 and copy down
C3 = C2 + D2 and copy down
F2 = DEGREES(ATAN(C2/B2)) and copy down
G2 = F2 – F3
“There was a man in the land of Uz, whose name was Job; and that man was blameless and upright, and one who feared God and shunned evil. And seven sons and three daughters were born to him. Also, his possessions were seven thousand sheep, three thousand camels, five hundred yoke of oxen, five hundred female donkeys, and a very large household, so that this man was the greatest of all the people of the East.
And his sons would go and feast in their houses, each on his appointed day, and would send and invite their three sisters to eat and drink with them. So it was, when the days of feasting had run their course, that Job would send and sanctify them, and he would rise early in the morning and offer burnt offerings according to the number of them all. For Job said, ‘It may be that my sons have sinned and cursed God in their hearts.’ Thus Job did regularly.” —Job 1:1-5
Job is probably the oldest book in the Bible and waaay fascinating to boot. If you haven’t read it you’re missing out…and if you haven’t read it critically, you might as well have not read it at all. The best part comes near the end, but I’m not giving it away! The reason for quoting Job is to show that wealth in the ancient world was measured in much more material, meaningful ways than it is today–smart, hard working kids who had themselves survived to reproductive age, herds of livestock with reliable production, lackeys and indentured servants. The first few verses demonstrate the story hales from the early days of agriculture and animal husbandry.
Our post-industrial global society is “richer”, but the definition of wealth has gotten pretty ambiguous. A “mass-affluent” individual or family may have a gang of financial assets and high potential earnings. But whether or not those assets behave as capital, let alone as strategic capital, is dependent on a host of factors. Job’s capital, on the other hand, was measured in animals, workers, land, and family alliances. Anybody who wouldn’t trade a few pieces of paper for that hoard is truly without hope.
Pre-1840 (approximately), relative wealth and income for 90% of the world’s population mostly depended on weather conditions and the rate of nitrogen replacement into the soil. These could not be measured or predicted at the time, and rightly belonged to the realm of the gods. With the use of salt-peter from Chile, and later through the Haber-Bosch process, the nitrogen replenishment limit on agricultural productivity was broken. Thanks to steam- and internal-combustion engines, dependence on weather was overcome through global trade. Fractional reserve banking lifted the precious metal limit on currency production.
Today our conventional notions of wealth and income are measured in currency, which wasn’t the norm even 120 years ago. The value, production, and distribution of currency are entirely social constructs. However, financial and economic systems are damned complex, subject to non-linearity and probability. Because of this, there is a tendency to ascribe human personalities and divine attributes to dumb money. This vestigial tendency shows up in everything from Adam Smith’s “Invisible Hand” to the ultra-hokey modern day “Law of Attraction”.
Bah!–Enough of the garbage! Bill McBride at Calculated Risk has put together a neat-o graphic showing job losses and recoveries in US recessions. It’s shoplifted below:
Bill’s right to focus on employment when discussing recessions. As Dr Hall notes, modern recessions tend to be mild in terms of changes in absolute output, but are as bad as ever–worse even–in terms of job recovery. If this is the case, what can be measured to better understand and “rate” modern recessions and recoveries? Are there trends from past recessions that can help? If interest rates (determined by the Federal Reserve Bank), and fiscal deficits (from the US treasury) can be thought of as control mechanisms, to what extent are they effective?
The current US recovery has been described by some “as the weakest since WWII“. It’s a fair assessment, and I won’t mince words–the human cost has been terrible. But from both Dr Hall’s work and McBride’s graph, it appears that job losses and recoveries have been trending longer since the 1980 recession. This may just be a visual anomaly. To try to score post-WWII US recessions and recoveries in terms of their length and depth, I measured the time from previous employment peak to recovery in months and divided it by the depth of the job loss trough in percent for each recession on Bill’s graph. I call this the Job Recovery Score.
I put the scores and the years of recession-starts into Excel and graphed. To score the 2007 recession, I used Bill’s job recovery projection. I don’t normally include Excels goofy rolling trendlines in real work, but this time it highlights a pattern in the data that’s hard to see from the data points alone:
When analyzing recessions, there are always a few problems. For starters, the data set is small. Furthermore, recessions are often caused and mitigated by extraneous events. For example, the 1969 recession began at the same time as the Tet Offensive in Vietnam. The 1974 recession started with the loss of Middle Eastern petroleum exports to North America and Europe. Martin Feldsman’s data shows that it ended with growth in agricultural exports from the United States to the USSR, America’s first experiment in the “Oil for Food” trade. Job recovery after the 2001 recession was delayed by the terrorist attacks of 9/11 and the march to war in Iraq. Nevertheless, there’s a roller-coaster like pattern to the job recovery scores over time, and the scores of the last ~20 years are much more negative than those before them. I isolated the “peak points” and labeled them in red, and the “trough points” and labeled them in green. I did a linear fit of those data points:
I was surprised to see strong correlations–0.964 for the peaks, and 1 for the troughs. While it can be hoped that the next recession will have a less-negative job recovery score, based on this analysis we can expect that it won’t. If the pattern holds, the next employment recession will be shallower, and the relative recovery much slower than the current one. Why this is happening–have monetary and fiscal responses become less robust over time? I obtained the historical Federal Funds Rate data and compared it with the recession score graph:
The Federal Reserve tends to raise interest rates during expansions to reduce inflation, and drops them during recessions to promote recovery. Based on the Federal Funds Rate, it’s pretty obvious that this policy response has been relatively mute since about 1990, but it has fallen more and risen less. On the whole, interest rates have been much lower over the last 20 years than during the time from the late 1960s to early 1980s. The next image shows the fiscal response in the form of Federal deficit spending as a percentage of nominal GDP:
Based on this chart, we can see that the fiscal response to recessions has, in fact, been proportionately stronger since 1970 than it was from 1946-1969. Based on policy interest rate data and the Federal deficit data, I think the policy responses have not weakened since the 1948 recession; they have just become less effective. This may be the least desirable conclusion of all. Tax cuts and spending increases at the Federal level, even coupled with aggressive monetary policy from the Federal Reserve, have not been sufficient to solve the problem of long employment recessions in the United States.
In light of these problems, it’s tempting to point to trade deficits and uncontrolled immigration as causes of the seeming impotence of public policy to promote post-recession employment recovery. Unfortunately, the data on these effects are mixed at best. While there are many social problems caused by these issues, worker displacement for starters, it’s doubtful that they are the driving force of policy impotence. It’s clear also that privately owned and managed institutions have not solved the problem either.
To end this post, I will barf out a few of my own opinions. Feel free to stop reading now if you don’t want to hear it. At the tail end of a May 20, 2013 discussion of economic inequality and growth at the City University of New York, Paul Krugman noted that the two periods with the highest economic growth in the US coincided with the Gilded Age (1870-1900) and the Post-WWII boom (1946-1973). During the Gilded Age inequality grew fast, and during the Post-WWII Boom inequality declined. He expected a closer relationship between inequality and growth, whether it was positive or negative. I think Dr Krugman’s statement has to be examined from the perspective of real capital formation. During the Gilded Age, the policy of the US government was to distribute land to settlers who were willing, able, and (unfortunately) racially favored to work it. We should not kid ourselves–the Homestead Acts were coupled with one of the most horrific campaigns of genocide against a native population ever seen in human history. But from an economic perspective they were a transfer of capital in the form of land into the hands of relatively cash-poor individuals. The financial inequality of the Gilded Age was mitigated by the largely agrarian nature of society and the availability of free or cheap land. Even if a homesteader could not profitably work a claim, he or she could reasonably expect make money by selling it.
My conjecture is that the employment problem we face is symptomatic of a rising difficulty in the accumulation of strategic capital. Sure, there’s plenty of liquidity and corporate money in the US and around the globe. But the flow is mainly controlled by a relative few individuals, and since they are a small group, their knowledge and interests are inherently too small to fill all potential markets. For people of average means or less, there are few, if any, routes to acquiring the skill, equipment, land, and cash one needs to start a viable, profitable business. Furthermore, there are virtually no simple ways for a person of modest means, education, and average health to build his or her real income. A few policy proposals that may be worth considering are:
- Sponsoring increased mentoring and small business grants to potential entrepreneurs in low- to moderate-income areas
- Increase public or public-private employment opportunities, such as one where government covers half the cost of employing low income individuals. My first job was actually through a program like this.
- Develop programs and partnerships to assist low- and moderate-income households with the purchase of solar panels or wind turbines, and grant consumers the right to sell power back to the grid
- Encourage profit-sharing programs in mid-sized businesses, and dividend-paying stock compensation for workers in publicly traded companies
- Fund modest lifetime income payments which could be earned by low-income and long-term unemployed workers through paid or volunteer work. Payments could be managed through insurance annuities.
Anyway, interesting topic that I’d thought about for a while and finally had time to look at. Disclaimer: economics isn’t my specialty and I really didn’t derive anything. What are your thoughts?
PS–you can like The Sexy Universe on Facebook right now!