Calculation of yearly stock returns through yearly share prices- loop programming STATA

[Jonathan Smits]

Dear Statalists,

I have one fundamental question that may need your kind help. Think it is a simple loop programming. I need to calculate stock returns for each firm (panel data), identified through a FirmID, as 1 year buy-and-hold stock returns through the annual/yearly share prices. A short version and generalized version of my data information has been shown as below:




Could you please show me how to deal with the loop coding?

Many thanks in advance.

Best!

[William Lisowski]

Jonathan, please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using CODE delimiters, and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

The theme of this answer, and the comment in my answer to your other post, is that the more you help others understand your problem, the more likely others are to be able to help you solve your problem.

And in particular, "I want to retype sample data from a picture so I can solve this problem" was said by nobody ever. Please post sample data in a form that is useful for experimentation and testing.

Added in editing: I forgot, it was said by Clyde at least once. Maybe he'll see this question and retype your data.

Added in further editing: you should explain how one would calculate the stock return by hand for one or two observations of your sample data. I do not deal with stock, so "1 year buy-and-hold stock returns through the annual/yearly share prices" means little to me, and perhaps to others. Again, the more we understand what you want the better we can answer your questions.

[Jonathan Smits]

Jonathan, please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using CODE delimiters, and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

The theme of this answer, and the comment in my answer to your other post, is that the more you help others understand your problem, the more likely others are to be able to help you solve your problem.

And in particular, "I want to retype sample data from a picture so I can solve this problem" was said by nobody ever. Please post sample data in a form that is useful for experimentation and testing.

Added in editing: I forgot, it was said by Clyde at least once. Maybe he'll see this question and retype your data.

Added in further editing: you should explain how one would calculate the stock return by hand for one or two observations of your sample data. I do not deal with stock, so "1 year buy-and-hold stock returns through the annual/yearly share prices" means little to me, and perhaps to others. Again, the more we understand what you want the better we can answer your questions.

By using STATA, I want to calculate stock returns through prices of firms. I would like to calculate the 1 year difference in stock prices to calculate the stock return.

For example, we have price 32.13 on 2008 as first (panel)data for firmID 1075 and a stock price of 36.51 on 2009 for FirmID 1075. So the calculation for yearly stock return is

log(36.51/32.13)



where denotes price on year .

This needs to be done for every year of each firm(ID). The problem is that, first I do not necessarily know how to do this 'automatically' in terms of loops and second, what to do with the first available year? Because I assume that that would be 0...... which is not true in reality..

[William Lisowski]

For your first available year, the value will need to be a Stata missing value, because you have no way to measure the denominator of the ratio of which you take the log.

With that in mind, then, something like the following may do what you need.

bysort FirmID (Year): generate return = log(SharePrice/SharePrice[_n-1])

See, no looping is necessary!

Note that _n is the observation number within the value of FirmID and since SharePrice[0] will be a Stata missing value (there is no observation 0) then the generated value of return will be a Stata missing value as well.

Here is a demonstration.

. generate ret = log(sp/sp[_n-1])
(1 missing value generated)

. list

     +---------------+
     | sp        ret |
     |---------------|
  1. |  1          . |
  2. |  2   .6931472 |
  3. |  4   .6931472 |
  4. |  8   .6931472 |
     +---------------+

[Jonathan Smits]

For your first available year, the value will need to be a Stata missing value, because you have no way to measure the denominator of the ratio of which you take the log.

With that in mind, then, something like the following may do what you need.

bysort FirmID (Year): generate return = log(SharePrice/SharePrice[_n-1])

See, no looping is necessary!

Note that _n is the observation number within the value of FirmID and since SharePrice[0] will be a Stata missing value (there is no observation 0) then the generated value of return will be a Stata missing value as well.

Here is a demonstration.

. generate ret = log(sp/sp[_n-1])
(1 missing value generated)

. list

+---------------+
| sp ret |
|---------------|
1. | 1 . |
2. | 2 .6931472 |
3. | 4 .6931472 |
4. | 8 .6931472 |
+---------------+

Thank you very much, I have however a couple of questions:

1. What is the intuition of adding 'by' to ''sort''?

2. Is there any difference between bysort FirmID (Year) and bysort FirmID Year, if so what?

[Jonathan Smits]

I know that “sort FirmID year” before generating the variable won't suffice, because the [_n-1] for the first observation of Firm x refers to the last observation of Firm Y. But what does adding by do exactly and what is the intuition behind that command??

[William Lisowski]

You will find the explanation provided by the output of help bysort helpful in understanding the use of the by prefix. And for a better understanding, you will benefit from reading the two sources in the Stata PDF documentation linked to from the start of the help output.

The help command should be your first stop in understanding unfamiliar Stata code. That you didn't acknowledge looking at help suggests you are not as familiar with Stata's documentation tools - the help command and the PDF documentation - as you need to be to make effective use of Stata. I'm sympathetic to you as a new user of Stata - it's a lot to absorb. Nevertheless, I'd like to encourage you to take a step back from your immediate tasks.

When I began using Stata in a serious way, I started, as have others here, by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. There are a lot of examples to copy and paste into Stata's do-file editor to run yourself, and better yet, to experiment with changing the options to see how the results change.

All of these manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu. The objective in doing the reading was not so much to master Stata as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and PDF manuals.

The Stata documentation is really exemplary - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively.

[Jonathan Smits]

You will find the explanation provided by the output of help bysort helpful in understanding the use of the by prefix. And for a better understanding, you will benefit from reading the two sources in the Stata PDF documentation linked to from the start of the help output.

The help command should be your first stop in understanding unfamiliar Stata code. That you didn't acknowledge looking at help suggests you are not as familiar with Stata's documentation tools - the help command and the PDF documentation - as you need to be to make effective use of Stata. I'm sympathetic to you as a new user of Stata - it's a lot to absorb. Nevertheless, I'd like to encourage you to take a step back from your immediate tasks.

When I began using Stata in a serious way, I started, as have others here, by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. There are a lot of examples to copy and paste into Stata's do-file editor to run yourself, and better yet, to experiment with changing the options to see how the results change.

All of these manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu. The objective in doing the reading was not so much to master Stata as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and PDF manuals.

The Stata documentation is really exemplary - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively.

Thank you very much!!

[Jonathan Smits]

For your first available year, the value will need to be a Stata missing value, because you have no way to measure the denominator of the ratio of which you take the log.

With that in mind, then, something like the following may do what you need.

bysort FirmID (Year): generate return = log(SharePrice/SharePrice[_n-1])

See, no looping is necessary!

Note that _n is the observation number within the value of FirmID and since SharePrice[0] will be a Stata missing value (there is no observation 0) then the generated value of return will be a Stata missing value as well.

Here is a demonstration.

. generate ret = log(sp/sp[_n-1])
(1 missing value generated)

. list

+---------------+
| sp ret |
|---------------|
1. | 1 . |
2. | 2 .6931472 |
3. | 4 .6931472 |
4. | 8 .6931472 |
+---------------+

After testing the code, it seems that my dataset created missing values. This is absolutely normal, because the missing values are because these are the base years of firms so there is no earlier year to estimate any returns. But how to handle that in my (regression)-analysis? Do I just need to replace them with zero's or..?

[William Lisowski]

Leave the missing values as they are; the regress command will ignore the observations for which one or more of the variables in the model are missing. There is no justification to replace the missing value with 0, or with 42, or with 666, or with 3.14159, or with any other non-missing value.