Monthly returns sticking to the last observation

[Dmitri]

Hi guys!

I have a trouble calculating monthly returns on stocks. I have daily closing prices and need to get monthly returns from them sticking to the last observation, not the first one.

I mean the following. For example my observations start at the 1st of January and end at the 15-th of March. I need to calculate returns (like stock/stock(-1)-1) on stock from the 15-th of January till the 15-th of February and from the 15-th of February till the 15-th of March. When I just convert daily prices to monthly ones, I get 1 Jan - 1 Feb and 1 Feb - 1 March returns. I hope that you understand what I mean. I need to use monthly returns, but want to include the most recent data in calculations.

Would you be so kind to help me with this

[EViews Chris]

I'm not sure exactly what you're trying to calculate here, but here are some ideas.

You can calculate the number of observations required to be 'one month earlier' using the following expression in a seven day daily workfile:

Code: Select all

series onemonth = @datediff(@date, @dateadd(@date, -1, "month"), "d")

If you are in a five day daily workfile (monday to friday), you can use:

Code: Select all

series onemonth = @datediff(@date, @dateadd(@date, -1, "month"), "b")

Note that some values are NAs because the date 'a month before' some other date isn't well defined for all days of the month. For example there is no day 'a month before' the 30th of March because there is no 30th of February.

Once you've got the number of days, you can calculate the return over a month earlier for each day by using the series onemonth as a lag index

Code: Select all

series xreturn = (x - x(-onemonth)) / x

From there you can simply use a sample to view any day of the month you're interested in. For example:

Code: Select all

smpl if @day=15

will select only the 15th day of each month.

If you need to extract the results for a particular day of the month into a monthly page, simply use a sample like the one above in a link to the daily page from the monthly page.

Code: Select all

link xreturn.linkto(smpl=if @day=15) dailypage\xreturn

[Dmitri]

Thank you Chris!

That's exactly, what I wanted! Linking this data to the monthly page is just what I need.

[Dmitri]

Hi Chris!

I just now had time to test the code and have some difficulties. Currently I have 2 pages: 5-day week named "daily" and monthly named "monthly". The code is the following:

Code: Select all

'9th Feb is the last date in observations pageselect daily for %x "stock1" "stock2" series month = @datediff(@date,@dateadd(@date,-1,"month"), "b") series {%x}_return = ({%x}-{%x}(-month))/{%x} next pageselect monthly for %x "stock1" "stock2" link {%x}_return.linkto(smpl=if @day=9) daily::{%x}_return next

The problems I have are the following:
1. I have data only for business days, so when I use smpl=if @day=9, then I have too many NAs, because the 9-th is often a weekend. Is it possible to add some kind of a loop to take some other close date if the one from @datediff(@date,@dateadd(@date,-1,"month"), "b") if it is not a business day or void this problem in some other way?
2. The other problem is that values in monthly sheet do not match the daily sheet if I link them. They are most probably averaged, could you please help me to avoid it?

Dmitri

[Dmitri]

Well, of course the line

Code: Select all

series {%x}_return = ({%x}-{%x}(-month))/{%x}

should be changed to

series {%x}_return = ({%x}/{%x}(-month))-1

but it's irrelevant to the problems.

[Dmitri]

And the last thing.

I was also thinking about using last 4 weekly returns instead of monthly ones, because my last obeservation will always be on Friday. If to use ln(stock1/stock(-1)), it's possible to sum the returns. What do you think about it? It is possible to do?

[EViews Chris]

If you change the smpl condition in the link to an inequality and use 'last' as the conversion method, you will get the last day on or before the 9th day. eg.

Code: Select all

link xreturn.linkto(smpl=if @day<=9, c=last) dailypage\xreturn

The 'c=' option lets you choose how to combine values from the daily file into the monthly file. The default is to average.

I'm not sure what you're getting at with your question about 4 weekly. In a regular monday to friday workfile, four weeks earlier is simply x(-20), so you can construct a four week return using x/x(-20).

It's true that you could also use a moving sum of the daily log returns if you wanted to do it that way. Note that EViews has a function @movsum() that you could use to do this.

[Dmitri]

Hi Chris!

Thank you very much for information!

Dmitri

[Dmitri]

Hi,

The first part of the code works fine, the problem I have is with linking to the monthly page. I tested your code and it doesn't seem to work. c=last does nothing, though if to change it manually in the series properties, then it works. After I found how to change average to last manually I don't really need this c=last, but neither smpl=if @day<=9, nor smpl="if @day<=9" also produce results. I tried to set this sample condition separately and it is shown on the top of the page, but data isn't filtered for some reason. Do you know what can be the problem?

I use eviews 5.0, but old version shouldn't be a problem, because all these features are mentioned in documentation for the 5th version. Does this code work on your eviews version? It would be great if you could attach a small example of the working code and couple of sample observations.

Thank you very much for your help!

Dmitri

[EViews Gareth]

I believe that the code will work in EViews 5. Try running the following program:

Code: Select all

create(page=dailypage) 5 1990 2000 series xreturn=@trend pagecreate m 1990 2000 link xreturn.linkto(smpl=if @day<=9, c=last) dailypage\xreturn

It runs fine for me in EViews 5.1

[jlsmith09012]

Sorry if the proper etiquette is to open a new thread, but this is relevant to the previous, so I thought I would append. In one of the suggestions for calculating weekly or monthly returns from daily return data, I think Chris mentioned that @movsum could be used. Pardon my ignorance, but say I want to calculate a weekly return. I can't figure out how to make @movsum sum the first five observations (1 - 5) and then sum the next five observations (6 -10). As I understand it, it will sum 1 - 5, then 2 - 6, etc. Am I missing something? Is there a way to tell it to sum 5 and then skip 5? Of course, the issue is more difficult, because every week doesn't have 5 trading days. Even if I can use MOVSUM to sum every 5 days, that will not exactly mesh with a week. Any ideas are greatly appreciated, with the understanding that I may need to pull my data weekly or monthly. For practical reasons, that is the least preferred option (as my weekly and monthly data do not include dividends, which I want to use in my return calculation.

I am using Eviews 7. Thanks in advance.

Jeff