calculating r-squared

Thanks:  0
Likes:  0

1. ## calculating r-squared

Hi,

I'd like to calculate the r-sqaured of the S&P 500 performance in an election year to see if that has any effect on the market's performance.

The data I have is below. The election years start in 1928 and are every 4 greater than that. How would I set it up to use the rsq forumla?

Thank you.

S&P 500 Index
1926 12%
1927 37%
1928 44%
1929 -8%
1930 -25%
1931 -43%
1932 -8%
1933 54%
1934 -1%
1935 48%
1936 34%
1937 -35%
1938 31%
1939 0%
1940 -10%
1941 -12%
1942 20%
1943 26%
1944 20%
1945 36%
1946 -8%
1947 6%
1948 6%
1949 19%
1950 32%
1951 24%
1952 18%
1953 -1%
1954 53%
1955 32%
1956 7%
1957 -11%
1958 43%
1959 12%
1960 0%
1961 27%
1962 -9%
1963 23%
1964 17%
1965 12%
1966 -10%
1967 24%
1968 11%
1969 -8%
1970 4%
1971 14%
1972 19%
1973 -15%
1974 -26%
1975 37%
1976 24%
1977 -7%
1978 7%
1979 18%
1980 32%
1981 -5%
1982 21%
1983 23%
1984 6%
1985 32%
1986 18%
1987 5%
1988 17%
1989 31%
1990 -3%
1991 30%
1992 8%
1993 10%
1994 1%
1995 38%
1996 23%
1997 33%
1998 29%
1999 21%
2000 -9%
2001 -12%
2002 -22%
2003 29%
2004 11%
2005 5%
2006 16%

2. ## Re: calculating r-squared

Easy way - create a line graph of the data - years on x axis, percentages on y axis. Add a trend line and in the trendline options ask it to display the equation and rsq value. This gives you the added value of a visual display of the data - essential, I would say.

3. ## Re: calculating r-squared

This is great. Thank you.

4. ## Re: calculating r-squared

Bit late now but creating the chart and rsq value as described above doesn't tell you about the relationship between election years and the S&P index. It tells you about the relationship between the year (as a number) and the S&P index.

To look at the relationship between election years and the S&P in terms of the r squared...let's say your years are in A1:A81 and your S&P indices in B1:B81...

In column C, put a 1 for each election year and a 0 for each non-election year. For example, in the first 4 cells in column C put the values 0,0,1,0. In cell C5 put =B1, and then drag that formula to fill the rest of the cells.

Then in an empty cell put =CORREL(B1:B81,C1:C81)^2. That will give you the r square.

Luckily, your conclusions won't change much.

For what it's worth, r square isn't a very good tool when one or more of the variables is categorical (as election year is in this case), but that takes us out of the scope of this board...

5. ## Re: calculating r-squared

slomobonobo,

Thanks very much for this. I realized as I finished this that r-squared isn't the best indicator of this. I belive it is the f-score, is that correct?

6. ## Re: calculating r-squared

How do I insert the ^ symbol in a formula?

I've searched high and low, and cannot find it anywhere!

7. ## Re: calculating r-squared

Originally Posted by fastglass
How do I insert the ^ symbol in a formula?

I've searched high and low, and cannot find it anywhere!
Shift+6....?

8. ## Re: calculating r-squared

Not sure I understand the question - you insert it in a formula the same way you inserted it in your question - Shift+6.

9. ## Re: calculating r-squared

lol!!!

I've just spent about 30minutes looking for an 'alt' shortcut for this, and it's staring me right in the face on my '6' key!

Sorry for being so dense guys!

10. ## Re: calculating r-squared

Originally Posted by fastglass
lol!!!

I've just spent about 30minutes looking for an 'alt' shortcut for this, and it's staring me right in the face on my '6' key!

Sorry for being so dense guys!
In terms of all the dense things I've done, that wouldn't make the top 20.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•