interpolation to specific sequence of dates

Dear all,

I have a panel data set of prices of goods that vary across time and countries.

The data are in an excel file

country  dates                price of good k
1         23/11/08           2
1    28/12/08                   3
1     25/01/09                   4
1    22/02/09                   5
1    29/03/09                  6
1  26/04/09                   32
1  24/05/09                  23
1  28/06/09                   32
2   26/10/08               45
2  23/11/08                 46
2  21/12/08               90
2  18/01/09                54
2  15/02/09                65
2   16/03/09               77
2  12/04/09                    7
2   10/05/09                   6

the start and end date of the time series for countries 1 and 2 are different. For example, for country 1 the time series begins on "23/11/08" while for country 2 the time series begins on "26-10-2008”.

My data on prices are available every 28 days (or equivalently every 4 weeks). But in some cases I have jumps (35 days or 29 days instead of 28 days). For example from the above table we have such jumps: from "28/12/08" to "28/12/08" , from 22/02/09" to "29/03/09", etc

My goal is to have a unified sequence of dates across countries. So, to achieve this I want to take what I have and calculate monthly average prices and also report how many prices those averages are based on. I suppose that I will still have gaps and may well need to interpolate. I would be grateful to you if you could provide some coding in order to achieve these 2 goals

Thank you in advance,

Answer 1

[Walter Roberson]

As discussed in your previous topic http://www.mathworks.com/matlabcentral/answers/40263-analyzing-assymetric-data-sets you can use interp1()

Nominal_Prices = interp1( Known_Datenums, Known_Prices, Nominal_Datenums, 'linear')

Nominal_Datenums would be the datenums at which you want to know the prices. You might find addtodate useful for constructing those.

[antonet]

thank you Roberson. It is possible to give me a practical example adjusted to the example that I provide above (so at to show me how to use addtodate as well).

I would be grateful to you if you could provide me the exact code that calculates monthly average prices for each country separately given what we have.

Thank you again

Answer 2

[Pavel Gorodetsky]

first you have to convert the dates from strings to number, say day number from january 1st 1900 (or whenever Matlab/Exec start counting. i'm sure there's a function that does just that.). then break your data into several variable,

dates1, price1, dates2, price2

with dates1 being the dates of price in country 1 etc. now you can use the interp1 function to interpolate prices in one country on the other country dates:

price1_2 = interp1(dates1, price1, dates2);

this will give you the prices in country #1 on dates of country #2 (price2 and price1_2 will have the same number of elements). read the help of interp1 function to see which interpolation method is best for you.

[antonet]

thank you for your suggestions but what I am asking is a bit different. I do not want to interpolate the dates of one country to the dates of the other country (which is also another correct altenative ) but I want to take what I have and calculate monthly average prices for each country separately using the existing sequence of dates. Is there any code that could allow me to do this?

thanks again