Request for help with "4 weeks ending" data

[AndrewB]

Hi,

I have an Excel spreadsheet with a large number of series that are for activity in "the 4 weeks ending". So they start from the 4 weeks ending Sun 6 Jan 2002 and run up to the 4 weeks ending Sun 18 March 2012. What I would like to end up with in Eviews is a set of monthly data based on these 4-week ending data. The only way I have managed to achieve this so far was quite long-winded. Basically, I padded out the data in Excel so that they were weekly rather than 4-weekly, filling the missing observations with #N/As. This took a while! I then imported the now weekly data into a weekly page in Eviews, converted the page frequency to daily, interpolated the data, and finally copied the data into a monthly page telling it to take the final observation when converting from a high to a low frequency.

Is anyone able to tell me a better way to do this? I am hoping there is one!

Thank you,

Andrew

[EViews Gareth]

Could you provide an example of the Excel file/format?

[AndrewB]

Hi Gareth,

I am attaching a small portion of the data we are trying to upload - just one series in fact. But this should give you an idea about the layout. If I open it using "File, Import Foreign Data as Workfile" it will give me an unstructured/undated workfile with two series objects called "Date" and "Series". If I then use "Proc ", "Structure/Resize" and try to convert the page to "Dated - specified by date series" and use "Date" as the date series, I get the error message "Frequency of date series could not be determined, or is not currently supported by Eviews" and I end up with a workfile that uses integer dates. The problem now is that I cannot copy to a new monthly page to convert the "4-week ending" data to monthly data. The only way I have found to do that so far is the very long winded way set out above!

Thank you very much for your help.

Andrew

[EViews Chris]

You're right - this is more painful than I would have thought.

What is actually going on is that the automatic frequency analaysis in EViews is looking at your data and figuring out that it is four-weekly, but unfortunately this isn't one of our standard supported frequencies, and things aren't handled particularly well. (I can't give you any good reason why this is not supported other than that I can't remember us having requests for it).

However, the step that you probably don't need to do is to pad out the series to weekly in Excel. Here's one possibility:

1) Import the xls file as unstructured/undated (i.e. just click on 'Finish' immediately)

2) Create an empty seven day daily workfile covering the date range of your data

3) Use Copy...Paste Special to move the series from the unstructured workfile into the daily workfile. Choose 'General match merge criteria', SourceID='date', DestID='@date'. This will create a daily workfile with 27 observations containing NAs between your data points. (You could go to a weekly workfile with 3 observations containings NAs between data points by the same method).

4) Use Proc...Interpolate to fill in the missings

5) Move series into a monthly file using whatever conversion method you want to summarise the interpolated daily data back down into monthly observations.

It's not ideal, but it's always going to be a bit messy doing operations like this that go from one frequency (four weekly) into another frequency (monthly) that is not aligned. Going via daily (like you were doing) probably is the best approach because it gives you a lot of control over how the conversion will be carried out.

Chris

[AndrewB]

Excellent. That has saved me a lot of time.

This is the first time I have used 4-week data. For what it's worth, the data I am looking at relate to retail sales. I think shops often report in 4-week blocks because (i) it ensures you have a fixed number of weekends in each observation and (ii) relative to weekly data, 4-week data are less volatile.

Anyway, thank you very much for your help.

[econworker]

Hi, my problem is opposite with this...I have 5 days a week daily data and I want to convert it to weekly data Friday to Friday, however what Eviews do is to end it Monday to Monday for 5 days a week and Wednesday to Wednesday for 7 days a week data.
Is there any solution that I can manage to convert my daily data to Friday to Friday or 4 days a week data? as all of my other data sets from the source that I obtained them end up at Friday.

Thanks for your help

[EViews Gareth]

Could you provide the data files?