reading weekly data with irregular date

[upani]

Dear All,

I want to read weakly time series trading data with irregular dates as some of the last closing dates of the week is holiday. I have 52 weeks per year. My data spans over 2006-2019.

Code: Select all

OPEN DATA "C:\Users\DELL\Desktop\crude.xlsx"
CALENDAR(PPY=52) 2006:1
DATA(FORMAT=XLSX,ORG=COLUMNS) 2006:01 2019:52 FP SP

While calculating summary statistics, I am getting the following output:
Series Start Size Mean Stan Dev Skew Exc Kurt Min Max
(a) Weekly Price Series (%)
Future Price 1/7/2006 676 4054.895 1099.850 0.479 -0.452 1827.000 7246.000
Spot Price 1/7/2006 676 4049.151 1098.576 0.466 -0.449 1783.000 7291.000
(b) Weekly log returns (%)
Future Return 1/7/2006 675 0.090 4.551 -0.482 2.784 -27.826 19.959
Spot Return 1/7/2006 675 0.092 4.757 -0.353 2.831 -26.373 22.690

So I am a bit confused about why 676 observations are coming instead of 728!

I would like to correct way of reading the data.

Regards,
Upananda

[TomDoan]

There has to be something about your calculation of the summary statistics which is restricting the range.

Your days of the week on your date field are jumping all over the place---are those actual observation dates, or are you altering them to create 52 periods per year? As you've written that, this will do some interpolating to use the data as attached to those specific dates. If you have set it up for 52 periods per year (which one observation a week won't do, as some years would have 53 weeks), then the "date fields" on the file should be 2006:1, 2006:2, etc. You can avoid any problems with the misinterpretation of the dates by adding the LEFT=2 field to your DATA instruction, which will skip the reading and processing of the date field on the file.

[upani]

Dear Sir,

Thanks a lot for your help. Yes, I have changed the data because of missing observations. As some of the last trading day of the week falls on holiday, i have shifted the day to the previous trading day of the week. As per your suggestions i will proceed.

Regards,
Upananda