Dealing with multiple observations per day

[moderator]

The CALENDAR(PERDAY=....) feature in RATS defines a fixed number of observations for each day, but you may have data that has differing numbers of observations from day to day (as with stock trade data). For example, you might have a spreadsheet with 10 rows of observations for the first day in your sample, followed by 25 rows for the second day, 18 rows for the third day, and so on.

Here's a sample program that shows how you can expand such a data set into the fixed perday setup supported by RATS. This assumes your spreadsheet includes YEAR, MONTH, and DAY columns in addition to the actual data, indicating the date of each data point. If you just have a column of dates in the spreadsheet, you can create these columns by using the =YEAR, =MONTH, and =DAY functions to create columns of YEAR, MONTH, and DAY numbers .

Code: Select all

*** Read in the data as undated:
open data mydata.xls
data(format=xls,org=columns) / asset year month day

*** Set NUMOBS equal to the total number of observations:
inquire(series=asset) start end
compute numobs = end

*** Set a variable called PERIOD to indicate the period
***  within a given day for each observation:


*** Initialize the series:
set period = 1

*** Walk through data set:
do n=2,numobs
 *** If were are still in the same day, one period later, set
 ***  PERIOD equal to 1 more than the value of PERIOD at the
 ***  previous entry. Otherwise (if we've moved on to a new day),
 ***  set PERIOD to 1:
 if year(n)==year(n-1).and.month(n)==month(n-1).and.day(n)==day(n-1)
   compute period(n) = period(n-1) + 1
 else
   compute period(n) = 1
end do

*** See the results:
print(window='check') /

*** Set a variable equal to the maximum number of periods:
compute maxperiods =  fix(%MAXVALUE(period))

*** Set the starting and ending year, month, and day values.
*** The FIX() function converts real values into integers. 

compute FirstYear  = fix(year(1))
compute FirstMonth = fix(month(1))
compute FirstDay   = fix(day(1))

compute LastYear  = fix(year(numobs))
compute LastMonth = fix(month(numobs))
compute LastDay   = fix(day(numobs))


*** Use these values to set a "perday" Calendar

cal(perday=maxperiods,d) FirstYear:FirstMonth:FirstDay

*** Set FIRST and LAST to the starting and ending
***  observations in the Perday Calendar:
compute first = FirstYear:FirstMonth:FirstDay//1
compute last  = LastYear:LastMonth:LastDay//maxperiods

*** Reset ALLOCATE to end of perday range:
allocate last

*** Initialize a new series to hold the expanded asset values:
set assetfull = %na

*** Walk through all periods. If the year, month, day, and period
***  of the source data matches the year, month, day, and period
***  of the calendar date, copy the source value into the new series
***  and increment the counter to point to the next source observation.

compute source = 1
do entry=first,last
 if year(source)==%year(entry).and.$
     month(source)==%month(entry).and.$
      day(source)==%day(entry).and.$
       period(source)==%period(entry)
   {
    compute assetfull(entry) = asset(source)
    compute source = source + 1
   }
end do

*** Check the results:
print(window='Final') first last assetfull