Need to calculate systematic skewness

[david55]

Hello

I need to write a formula for my data, but I'm not sure the way I write is the best.

I have two columns of data, let's say in column A and B, which are monthly returns of stock A (Ri) and market (Rm) respectively.

I need to calculate a value similar to covariance but not exactly that.

that is:
formula.jpg


I really appreciate your kind attention.

[david55]

the formula:

\1

[arlu1201]

Is this solved?

[MrShorty]

You have the formula, so how to put it into Excel. A sample spreadsheet might help us understand how your data is arranged in the spreadsheet. I'm going to assume you have a column containing Ri's and another column containing the Rm's. Here's a list of the built in functions to refer to: http://office.microsoft.com/en-us/ex...524.aspx?CTT=1

1) Calculate Ribar and Rmbar. Assuming these are supposed to be arithmetic means, you can use the AVERAGE() function for this.
2) Now, in adjacent columns to Ri and Rm, calculate Ri-Ribar and Rm-Rmbar for each point. This should be real easy with the right combination of relative and absolute references. I would probably also add a couple of helper columns to calculate (Ri-Ribar)*(Rm-Rmbar)^2 and (Ri-Ribar)*(Rm-Rmbar)^3
3) Use the SUM() function to return the summations of these helper columns, then manipulate as needed to get the final quantities.

[david55]

@arlu: not yet
@MrShorty: Your assumption is quite right. But I have over 400 columns under Ri category where if I follow your solution I have to do it one by one for each column. I thought maybe there is a function as a shortcut to do all this, maybe I have to create my own formula. Anyway, thank you for your kind attention.
Any other suggestions are most welcomed

[MrShorty]

But I have over 400 columns under Ri category where if I follow your solution I have to do it one by one for each column.

Without a sample of how your data are arranged in the spreadsheet, I will have to take your word for this. However, it seems to me that in many cases, with the right combinations of relative and absolute references, it should be a simple copy the first column - paste into the other 399 columns to get the results you want.
I also find in my own spreadsheets that, if the input data are not arranged so that I can accomplish something like this with a simple copy-paste, it is easier to rearrange the input data to make it conducive to a simple copy and paste, than it is to figure out how to work with the data in a less than ideal layout.

[david55]

skw.jpg
Here is the picture. For the matter of space, I could include all 480 columns, so actually Ri columns goes to column RQ. There is only one column of data for Rm.

[MrShorty]

It looks to me like it should be simple copy and paste. There is the question of where to put the intermediate calculations as well as the final results -- I can't be certain exactly where you would like to put them.

Is each column a separate set of Ri's, so you will eventually end up with 400+ individual gamma's and delta's, or do all 400 columns come together into a single result? How many rows is typical?

Based on the screenshot, I would be tempted to calculate Ribar and Rmbar in row 40 or row 50 (=AVERAGE(A3:A35) then copy across).
Then in row 51 to 85 (or 61 to 95) calculate the individual Ri-Ribar values (=A3-A$50 copied down and across).
In the Rm column, I would probably include individual (Rm-Rmbar)^2, ^3, and ^4.
In row 90 or 100 in each Ri column, compute the sums for the numerators. Maybe =sumproduct(A51:A85,$Q91:$Q125) where q91:q125 has the (rm-rmbar)^2. Copy across
In row 91 or 101 compute gamma =A90/sum($Q131:$Q165) where q131:q165 contains the (rm-rmbar)^3. Copy across
And so on until you get gamma and delta for each column.