@dateadd question

[javiersan]

EV7

Hi,

I have a set of financial series on d5 frequency and I want to obtain the data for each serties 1 week ago, 1 month ago, 1 quarter ago and 1 year ago with reference date the last available data point. For obtaining the value 1 month ago I have used the function

Code: Select all

%last_dt=@otod(@ilast({%ser})) !last_dt_n=@dateval(%last_dt,"mm/dd/yyyy") ... !first_dt_n=@dateadd(!last_dt_n,-1*!n,"mm") 'where !n is 1, 3 and 12

The last available data for some series today is as of May 31 and @dateadd seems to be looking for the data for April 31, which obviously does not exist. Do you have any easy solution that makes @dateadd default to the last day of the month in this case?

Thanks,

Javier

[EViews Gareth]

Add a day to the current date, take off a month, then take off a day. Thus you go from May31 to June 1 to May 1 to April 30.

[javiersan]

Thanks, but it won't work for February I guess...

[EViews Chris]

One approach would be to add something like this to handle the case where you've hit a non-existent day:

Code: Select all

if(!first_dt_n = NA) then 'find last business day of month !n months ago !first_dt_n = @dateadd(@dateadd(@datefloor(!last_dt_n,"mm"),-(!n-1),"mm"),-1,"b") endif

Note that you have several things that can go wrong here:
- there is no day a month earlier (eg. 30th of March -> 30th of February)
- the day one month earlier is a Saturday or Sunday and so does not exist in a five day daily workfile
- the day one month earlier has a missing value (or does not exist in an irregular daily workfile)

I think the code above should handle the first two of these.

If the third case is a possibility, you'd probably need to go about things a bit differently and use a sample to set all the values the observations that are eligbile to be included (eg. using the @datediff() function) and then pick off the last of these values (using @last()).

[javiersan]

Thank you very much for your both your replies. In the end I went for subtracting 30 calendar days, which is a second best but avoids all these problems.

Regards,

Javier