[Jana He]

Thanks a lot for your help! But this is not exactly what I look for. I try to explain it again: The goal is to look what the share of stock sales with a loss in each month is, e.g. in January 20% of overall stocks sold are sold with a loss. And I want to do the same with stock sales with a profit (profti sale). For example, 55% of all stocks sold in March are sold with profit. My data set only shows sales and purchases for each investor and I wanted to create two variable which show me the purchase price of a stock and the sale price of a stock. And these two variables match, so the first line in variable buy price represents a purchase of investor X of stock Y and the equivalent holds for the variable sellprice, that means in the first line of this variable I find the sell price of investor X of stock Y. In a second step, I then would have created an other loop including an if condition to count for every month how many stocks were sold with gain and how many with loss. for example my condition would be "if sell price > buy price" then add to a counter variable +1 with this I would have a new variable with 12 lines (for each month) which represents the amount of profit sales (because the condition is sell price > buy price meaning that the stock was sold with a profit. I hope this explanation helps and you understand what I want to do.

To show an example of what output I want to generate: If you look in my data from #14, the first investor who bought and sold the same stock is investor 3206. In line 5 he bought 1000 stocks of X to a price of 2.25 in 1991 and in line 6 he sold the 1000 stocks of X in December 1993 for 1.0312, as the sale price is lower than the buy price, he sold with a loss. Therefore my variable for loss sales would increase by 1 in line 12 (as the loss was realized in December).

[Hemanshu Kumar]

Jana He I think I need more to understand your data in #14.

Let us focus on the stocks for which there are sales.

The example you gave in lines 5 and 6 was easy: 1000 units are bought, and 1000 are sold, so it is easy to think about gain/loss.

But consider lines 9 and 10 -- 345.96 units were bought, but 369.231 were sold. How can we decide on gain/loss without knowing about the price at which the remaining units were bought?

Also, in line 12, 122.52 units were sold for a security for which no purchase information is available. The same situation is in line 13, where 194.999 units are sold for a security for which we again have no purchase information.

How do you want to deal with these problems? Or is your original data complete and only the data extract is incomplete?

[Jana He]

I would only focus on the sales where I can also find a corresponding purchase. My data set only spans from January 1991 to November 1996. Your example in line 12 only shows a sale, therefore I assume that the stock was purchased before 1991. I only consider sales where I also find the corresponding purchase and I also only consider the same amount of stocks sold and bought, therefore I would ignore lines 9 and 10.

Additionally, I adjusted my code. Now, the only thing that does not work is the generation of the variables buyprice and sellprice.

local count = _N
local a=0
local u=0
local j = 1


set trace on

forvalues j = 1/7{
     if stocks[`j']>0 & investor[`j'] == investor[`j'+1] {
         local u =`j'+1
    
    
        forvalues i = `u'/7{
            if investor[`j']==investor[`i'] & cusip[`j']==cusip[`i'] & stocks[`j']==-stocks[`i'] & price[`i']!=0 & price[`i']!=price[`j'] {
                local a = `a'+1
                *gen buyprice[`a'] = price[`j']
                *gen sellprice[`a'] = price [`i']
                continue, break
            }
            
            else if investor[`j']!=investor[`i'] {    
                continue, break
            }
            else {
                
            }
            
            
        }
     }
}

set trace off

In this code, I let the forvalues loop go from 1 to 7, Just to test whether it works, but the actual final code would go from 1 to "count" including all lines of the data set.

[Hemanshu Kumar]

Jana He

I only consider sales where I also find the corresponding purchase and I also only consider the same amount of stocks sold and bought, therefore I would ignore lines 9 and 10.

I see. You should note, however, that you will then end up missing out on cases like cusip 59408710 for investor 7109 (lines 90-92), where you have complete information, but purchases were made in two rounds of 100 and 50, but the sale of 150 was made in one go.

If you are generally happy with your code, except for the buyprice and sellprice, those are relatively easy to fix. Outside the loops, you should initialise

gen buyprice = .
gen sellprice  = .

and then inside the loop, you would have something like

replace buyprice = price[`j'] in `a'
replace sellprice = price[`j'] in `a'

which would change the buyprice and sellprice in observation `a'.

[Jana He]

You are correct, I will miss the mentioned case, but I don't know how to rewrite the code to include these cases.

Thank you very much! The code now works!

I am not quite sure whether this is the best way to meet my goal, so if you have other suggestions to get my desired result, I would appreciate it!