Multiple Sigma's working correctly in one formula

**Ernst88** · 05-13-2014, 11:07 AM

Hello Everybody,

I need some help with a formula that I'm trying to get into my excel file. I have a database with the buy and sell information of investors over 80 periods. I try to determine if they’re behavior (buy or sell) is influenced by the behavior (buy or sell) of other investors in the period before. I have found a formula that does this, but haven’t succeeded in getting it working in excel. The formula is:

∑(K) [∑(N(k,t) ∑N(k,t-1) ((D(n,k,t)-A(t))*(D(m,k,t-1)-A(t-1)))/N(k,t)*N(k,t-1)]

I added the colors to make the formula clearer. ∑(K) is the sigma for all the stocks traded in period t. N(k,t) represents the number of investors that trade stock k in period t and N(k,t-1) represents the number of investors that traded stock k in period t-1. Furthermore, D(n,k,t) is a dummy variable that will equal one (zero) if investor n is a buyer (seller) of security k in period t or t-1 correspondingly. The last dummy variable D(m,k,t-1) equals one (zero) if fund m (m≠n) is a buyer (seller) of stock k in quarter t-1.
In my database, I have a column with the period, a column with the stock number, a column with the investor number, a column with if they are a buyer or a seller in that period ( If the investors is a buyer the cell in this column will display a 1, and if the investor was a seller it will have the value of 0. So the dummy variables are accounted for!) , a column with the value of A in that period, and a column with the number of investors that invested in the stock in that period N(k,t).

So, First I have to calculate for a stock in period t: [∑(N(k,t) ∑N(k,t-1) ((D(n,k,t)-A(t))*(D(m,k,t-1)-A(t-1)))/N(k,t)*N(k,t-1)]

Second, It has to be done for all stocks K in that period: ∑(K)

To give an example of the first part of the formula: There are 5 investors that trade stock k in period t, and 7 investors that trade stock k in period t-1. For every of the 5 investors in period t the behavior (buy or sell) is compared to the behavior (buy or sell) of all 7 investors in period t-1. So for the first investor in period t, the behavior is compared to the behavior of investor 1,2,3,4,5,6,7 of period t-1. This will lead to one value. For the second investor in period t, the behavior is compared to the behavior of investor 1,2,3,4,5,6,7 of period t-1, this will also lead to one value. And so on for all 5 investors in stock k in period t. Then the values of these 5 different investors in stock k in period t is summed to get to correct value of the first part of the formula.

Second part of the formula: This has to be done for all stocks in that period, and summed. (this is the ∑(K) part)

My problem is to get the multiple sigma’s working: I have got all the values incorporated in my datasheet. Now the formula needs to recognize:
- that it will compare the investment behavior of every investor that trades stock k in period t with all the investment behavior of every investor that trades in stock k in period t-1. Incorporate this into one value.
- Sum these individual values of all investors that traded in stock k in period t. So you will get one value for every stock traded in period t.
- Then sum these values for every stock traded in period t.

I hope someone can help me,

Thanks in advance!

**shg** · 05-13-2014, 11:59 AM

In both this and your other thread, you're more likely to get useful assistance if you post workbooks. Read the forum rules for how.

**Ernst88** · 05-15-2014, 11:05 AM

My apologies, I was not aware this was possible. I added a worksheet with a small part of my data.

I did not succeed in computing the formula yet. Hope someone can help!WorkbookExcelForum.xlsx

**shg** · 05-15-2014, 11:51 AM

Sorry, your data invites more questions than I'm inclined to ask. Perhaps someone else can help.

**MrShorty** · 05-15-2014, 01:24 PM

When I put this sort of thing into a spreadsheet, I usually choose one of two approaches:

1) Use a VBA UDF. I often find that summations like this are easier to code in a symbolic language like VBA (where we can use appropriate loops to perform the summations) than directly in a spreadsheet. Do you have any experience with programming languages like VBA, C, Java, etc? Something like this code snippet:

summation=0
for smalli=1 to bigi
for n=1 to Nit
for m=1 to Nkt
'code to compute each term in summation
next m
next n
next smalli

2) If I don't want to code a UDF, I will usually build the summation as a helper column (or columns) in the spreadsheet. Then refer to the summation column in the final formula. I don't think I completely understood how to use that formula, but I hope you can see the basic strategy I'm trying to use in the attachment.

It looks like there is a bit of work ahead of you to pin this down. Let us know how we can help.