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 Gareth, EViews Jason, EViews Steve, EViews Moderator

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

Calculating weighted average of a time period

Postby mcline » 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?

trubador
Did you use forum search?
Posts: 1518
Joined: Thu Nov 20, 2008 12:04 pm

Re: Calculating weighted average of a time period

Postby trubador » 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...

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13306
Joined: Tue Sep 16, 2008 5:38 pm

Re: Calculating weighted average of a time period

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

Nifty
Follow us on Twitter @IHSEViews

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

Re: Calculating weighted average of a time period

Postby mcline » 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.

EViews Chris
EViews Developer
Posts: 161
Joined: Wed Sep 17, 2008 10:39 am

Re: Calculating weighted average of a time period

Postby EViews Chris » 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.

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

Re: Calculating weighted average of a time period

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

This does help, thank you for the assistance.


Return to “Data Manipulation”

Who is online

Users browsing this forum: No registered users and 15 guests