Hi all,
I have a dataset on stock short positions by country and position holder. The holders short a stock that is uniquely identified by Issuer or ISIN, but there are multiple holders for some stocks.
I first sort the dataset using,
I want to create a new column which has a running total of short positions by country, issuer/isin.
The rule is that for a date, the total is the position taken by the holder on that day and the sum of the latest positions taken by other holders.
I created an example dataset below due to confidentiality and manually added the last two columns to show how the rules work - for example, Row 6 has holder C's position plus A and B's latest positions. If the same holder reports a position at the next date, then we update the total with the new value..
The same holders A,B,C can also short other stocks X,Y etc. so the total also needs to work within the group issuer, sorted by date.
There are different numbers of holders and issuers within a country.
I am very new to Stata so any help would be appreciated,
many thanks!
I have a dataset on stock short positions by country and position holder. The holders short a stock that is uniquely identified by Issuer or ISIN, but there are multiple holders for some stocks.
I first sort the dataset using,
Code:
sort country issuer isin pos_date
The rule is that for a date, the total is the position taken by the holder on that day and the sum of the latest positions taken by other holders.
I created an example dataset below due to confidentiality and manually added the last two columns to show how the rules work - for example, Row 6 has holder C's position plus A and B's latest positions. If the same holder reports a position at the next date, then we update the total with the new value..
The same holders A,B,C can also short other stocks X,Y etc. so the total also needs to work within the group issuer, sorted by date.
There are different numbers of holders and issuers within a country.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str7 country str1(position_holder issuer) int isin float net_short_position str9 pos_date str13 calculation float new_column "Austria" "A" "X" 123 .55 "1-Jan-17" "0.55" .55 "Austria" "A" "X" 123 .49 "2-Jan-17" "0.49" .49 "Austria" "B" "X" 123 .52 "2-Jan-17" "0.52+0.49" 1.01 "Austria" "B" "X" 123 .62 "3-Jan-17" "0.62+0.49" 1.11 "Austria" "B" "X" 123 .71 "5-Jan-17" "0.71+0.49" 1.2 "Austria" "C" "X" 123 .5 "6-Jan-17" "0.5+0.71+0.49" 1.7 "Austria" "C" "X" 123 .6 "7-Jan-17" "0.6+0.71+0.49" 1.8 "Austria" "B" "X" 123 .75 "8-Jan-17" "0.75+0.6+0.49" 1.84 "Austria" "A" "X" 123 .55 "9-Jan-17" "0.55+0.75+0.6" 1.9 "Austria" "C" "X" 123 .8 "10-Jan-17" "0.8+0.55+0.75" 2.1 "Austria" "A" "Y" 456 .5 "1-Jan-17" "0.5" .5 "Austria" "B" "Y" 456 .58 "2-Jan-17" "0.58+0.5" 1.08 "Austria" "B" "Y" 456 .7 "3-Jan-17" "0.7+0.5" 1.2 "Austria" "C" "Y" 456 .6 "4-Jan-17" "0.6+0.7+0.5" 1.8 "Belgium" "D" "Z" 789 .48 "1-Jan-17" "0.48" .48 "Belgium" "D" "Z" 789 .4 "2-Jan-17" "0.4" .4 "Belgium" "E" "Z" 789 .55 "3-Jan-17" "0.55+0.4" .95 "Belgium" "E" "Z" 789 .62 "4-Jan-17" "0.62+0.4" 1.02 "Belgium" "F" "Z" 789 .67 "5-Jan-17" "0.67+0.62+0.4" 1.69 "Belgium" "E" "Z" 789 .59 "6-Jan-17" "0.59+0.67+0.4" 1.66 end
I am very new to Stata so any help would be appreciated,
many thanks!
Comment