Calculating weighted average of a time period

For questions regarding the import, export and manipulation of data in EViews, including graphing and basic statistics.

Moderators: EViews Jason, EViews Moderator, EViews Gareth, EViews Steve, EViews Pamela

Calculating weighted average of a time period

Postby mcline on Tue Dec 02, 2008 2:19 pm

Is it possible to convert data from monthly frequency to annual frequency, but employing a weighted average rather than just a simple straight average of the twelve values? If not, is there some kind of workaround that is possible?

The root of my problem is that I need to calculate an annual average production rate from 12 monthly production rates. One cannot simply average the 12 monthly values, because each month has a different number of days and therefore needs to be weighted slightly differently to calculate the annual rate. I cannot seem to find a straight-forward way to do this with Eviews. As a workaround, I am currently creating a link from a monthly workfile page to a daily workfile page, then creating a link to an annual workfile page from the daily one. This gives me the correct data, but since I have several decades of data, the daily workfile page has a huge number of observations that inflates the size of my workfile and makes Eviews take a very long time opening and saving the workfile.

Any ideas?
mcline
 
Posts: 3
Joined: Tue Dec 02, 2008 2:00 pm

Re: Calculating weighted average of a time period

Postby trubador on Tue Dec 02, 2008 4:18 pm

1-Create a workfile in daily frequency and create a series that each value equals 1. (e.g. series days = 1)
2-Copy and paste the series "days" both to your monthly and annual workfiles as value instead of link and select the "sum observations" option for conversion method. Now you have the number of working days in each month and in each year.
3-In the monthly workfile you can create your new production series multiplying by days. (e.g. new = old*days)
4-Finally, you can convert new monthly production series to annual frequency with the “sum observations” option and divide the resulting series with days that you have previously created in your annual workfile from the daily workfile.

Once you have computed the monthly and annual days series as value, you can delete the daily workfile and get rid of the unnecessary load...
trubador
Did you use forum search?
 
Posts: 1019
Joined: Thu Nov 20, 2008 12:04 pm

Re: Calculating weighted average of a time period

Postby EViews Gareth on Tue Dec 02, 2008 4:25 pm

Nifty
Follow us on Twitter @IHSEViews
EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
 
Posts: 7599
Joined: Tue Sep 16, 2008 5:38 pm

Re: Calculating weighted average of a time period

Postby mcline on Tue Dec 02, 2008 4:27 pm

Thanks for this method, it works very well. I just wish there was some inherent way to calculate this directly. The workfile I am working with has about 2000 series in it and having to create an intermediary series for each just clutters things up a bit.
mcline
 
Posts: 3
Joined: Tue Dec 02, 2008 2:00 pm

Re: Calculating weighted average of a time period

Postby EViews Chris on Tue Dec 02, 2008 6:11 pm

For what it's worth, you can calculate the number of days or business days (monday to friday only) in a month directly in the monthly page:

Code: Select all
series days = @datediff(@enddate, @date, "D")+1
series busdays = @datediff(@enddate, @date, "B")+1

This isn't quite as general as summing from the daily data since you can't drop days for holidays.

To go further, you can also calculate the number of days in the year that contains each month fairly easily:

Code: Select all
series year_days = @datediff(@dateadd(@datefloor(@date,"Y") ,1,"Y"), @datefloor(@date,"Y"), "D")


If you're feeling really brave, you can put this all together and calculate the weights required to go from monthly averages to annual averages all in one line:

Code: Select all
series weights = (@datediff(@enddate, @date, "D")+1) / @datediff(@dateadd(@datefloor(@date,"Y") ,1,"Y"), @datefloor(@date,"Y"), "D")

I'm afraid I can't think of any good way around having to do the weighted frequency conversion in two steps though (the multiply then the summation). I'll keep this in mind as a possible future extension of our paste operations.
EViews Chris
EViews Developer
 
Posts: 148
Joined: Wed Sep 17, 2008 10:39 am

Re: Calculating weighted average of a time period

Postby mcline on Wed Dec 03, 2008 6:47 am

This does help, thank you for the assistance.
mcline
 
Posts: 3
Joined: Tue Dec 02, 2008 2:00 pm


Return to Data Manipulation

Who is online

Users browsing this forum: No registered users and 1 guest