Handling holidays in financial time series

Question

I have two lists: list1and list2, which are financial time series with only 2 variables—date and price. The problem is: these financial time series occur in 2 different countries, which have holidays on different days. For instance, if the first list is related to an asset in the US, there will be no data for this asset on July 4th (due to the American national holiday).
So the first need here is to FILL this missing (price-)values with the price of the previous date.
The second need is to COMPARE these lists, which have different dimensions due to different national holidays occurring on different dates for each country. In this case, some dates (with corresponding price values) must be included in the lists.

Let me give an example. Consider:

list1 = {{{2007, 1, 2}, 12.04}, {{2007, 1, 4}, 11.51}, {{2007, 1, 5}, 12.14}}
list2 = {{{2007, 1, 2}, 20.91}, {{2007, 1, 3}, 24.34}, {{2007, 1, 4}, 24.67},
         {{2007, 1, 5}, 20.97}}

In this case it will be necessary to include in list1 the observation {{2007, 1, 3}, 12.04}.

Any idea of how can I do this in Mathematica?

---

Edited

Please consider the following real time series:

list1 = FinancialData["PETR4.SA", {"2007"}];
list2 = FinancialData["XOM", {"2007"}];

Now consider the Dimensions of both lists:

Dimensions[list1]
{245, 2}

Dimensions[list2]
{251, 2}

I need to create Modlist1 and Modlist2, so that both have Dimensions[] of order {257, 2}. This means: I need to include 12 observations in list1 and 6 observations in list2 to obtain the modified lists, both of same Dimensions[].

Answer 1

Okay, a fresh start.

fillForCompare[data1_, data2_] :=
 Module[{f1, f2, all, last},
   all = Union @@ {data1, data2}[[All, All, 1]];
   (f1[#] := last = #2) & @@@ data1;
   (f2[#] := last = #2) & @@@ data2;
   f1[_] := last; f2[_] := last;
   {all, f1 /@ all, f2 /@ all}\[Transpose]
 ]

Now:

{list1, list2} = FinancialData[#, {"2007"}] & /@ {"PETR4.SA", "XOM"};

output = fillForCompare[list1, list2];

output // Length

257

output ~Take~ 10 // Column

{{2007,1,2},11.06,20.46}
{{2007,1,3},10.68,64.81}
{{2007,1,4},10.45,63.6}
{{2007,1,5},10.13,64.05}
{{2007,1,8},10.21,63.54}
{{2007,1,9},9.98,63.05}
{{2007,1,10},9.92,62.08}
{{2007,1,11},9.91,62.08}
{{2007,1,12},9.9,63.54}
{{2007,1,15},9.84,63.54}

---

With requested changes:

fillForCompare2[data1_, data2_, fill_ : Missing[]] :=
 Module[{f1, f2, all, last = fill},
   all = Union @@ {data1, data2}[[All, All, 1]];
   (f1[#] := last = #2) & @@@ data1;
   (f2[#] := last = #2) & @@@ data2;
   f1[_] := last; f2[_] := last;
   Transpose /@ {{all, f1 /@ all}, last = fill; {all, f2 /@ all}}
 ]

{newlist1, newlist2} = fillForCompare2[list1, list2];

Length /@ {newlist1, newlist2}

{257, 257}

Here is a somewhat more optimized, though I fear harder to read, version:

fillForCompare3[data1_, data2_, fill_ : Missing[]] :=
 Module[{rls1, rls2, all, last = fill},
   all = Union @@ {data1, data2}[[All, All, 1]];
   rls1 = Append[(# :> (last = #2)) & @@@ data1, _ :> last] // Dispatch;
   rls2 = Append[(# :> (last = #2)) & @@@ data2, _ :> last] // Dispatch;
   Transpose /@ {{all, Replace[all, rls1, {1}]},
    last = fill; {all, Replace[all, rls2, {1}]}}
 ]

Answer 2

I believe this will work with one exception (if there is a discrepancy in dates at the first position). This can easily be taken into account, but I don't have time at the moment.

Note: holidays can take place in either list. It is for this reason that g calls f twice.

f[l1_, l2_] := 
  Module[{c = Complement[l2[[All, 1]], l1[[All, 1]]], j},
  If[c == {}, l1,
  (j = SortBy[First@(Append[list1, {#}] & /@ c), First];
  (Insert[j, j[[# - 1, 2]], {#, 2}] & /@ 
   Flatten[Position[j, {{_Integer, _Integer, _Integer}}]])[[1]])]]

g[ls1_, ls2_] := {f[ls1, ls2], f[ls2, ls1]}

Usage

{list1, list2} = g[list1, list2]
(*
{
{{{2007, 1, 2}, 12.04}, {{2007, 1, 3}, 12.04}, {{2007, 1, 4}, 11.51}, {{2007, 1, 5}, 12.14}}, 
{{{2007, 1, 2}, 20.91}, {{2007, 1, 3}, 24.34}, {{2007, 1, 4}, 24.67}, {{2007, 1, 5}, 20.97}}
}
*)

Answer 3

Assuming the input data is already sorted with respect to dates, the following inserts missing weekdays (missing because of holidays or other reasons) using Version 9 function DayCount:

 fillDtsF = # /. {a___, {dt1 : {_, _, _}, x_}, 
     {dt2 : {_, _, _}, y_},  b___} /; DayCount[dt1, dt2, "Weekday"] >= 2 :> 
      {a, Sequence @@ ({DatePlus[dt1, #], x} & /@ 
        Range[0, DayCount[dt1, dt2, "Weekday"] - 1]), {dt2, y}, b} &

Usage:

 fillDtsF /@ {list1, list2}
 (* {{{{2007, 1, 2}, 12.04}, {{2007, 1, 3}, 12.04},
      {{2007, 1, 4},  11.51}, {{2007, 1, 5}, 12.14}}, 
      {{{2007, 1, 2}, 20.91}, {{2007, 1, 3}, 24.34}, 
     {{2007, 1, 4}, 24.67}, {{2007, 1, 5}, 20.97}}}*)

where

list1 = {{{2007, 1, 2}, 12.04}, {{2007, 1, 4}, 11.51}, {{2007, 1, 5},  12.14}};
list2 = {{{2007, 1, 2}, 20.91}, {{2007, 1, 3}, 24.34}, {{2007, 1, 4},  24.67}, 
  {{2007, 1, 5}, 20.97}};

Answer 4

You'll find for other uses of your financial data that things run faster if you initially convert to absolute times.

list1[[All, 1]] = AbsoluteTime /@ list1[[All, 1]];
list2[[All, 1]] = AbsoluteTime /@ list2[[All, 1]];

---

This is the sort of problem that you'll probably find lots of different approaches posted. I'd take the complement and give it a null value -- though you can choose something else -- and make a new list. My stepwise approach is:

addToList = Complement[list2[[All, 1]], list1[[All, 1]]];
addToList = {#, Null} & /@ addToList;
list1 = Sort@Join[list1, addToList1]

then replace the nulls with the value immediately before it:

positions = Position[list1, {_,Null}];
(list1[[#, 2]] = list1[[# - 1, 2]]) & /@ positions

You would probably want to add a test to ensure that 1 does not exists in the list of positions (because if position #1 is one of your dates you have no previous value to replace the value with).

You could also use a rule replacement approach here but I am conscious of your financial data probably being a few hundred, or much more, elements and the different dates only being a handful. Therefore I am choosing Part because it should be more efficient.

Alternatively it is not really necessary to programmatically find the dates that are missing because these are known to you as a trader on financial markets from the published dates on which the various world exchanges operate. In other words addToList above can readily be constructed manually and saved permanently as a constant.

In any case, as a function:

newList[l1_, l2_] := 
 Module[{addToList, positions, list1 = l1, list2 = l2},  
  addToList = {#, Null} & /@ Complement[list2[[All, 1]], list1[[All, 1]]];
  list1 = Sort@Join[list1, addToList];
  positions = DeleteCases[Position[list1, {_, Null}],1];
  (list1[[#, 2]] = list1[[# - 1, 2]]) & /@ positions;
  list1
  ]

where the arguments to newList are the test list first and then the comparator list.

newList[list1, list2]
(* {{{2007, 1, 2}, 12.04}, {{2007, 1, 3}, 12.04}, {{2007, 1, 4}, 
  11.51}, {{2007, 1, 5}, 12.14}} *)

and to operate on list2

newList[list2, list1]

To test the efficiency of methods you could use FinancialData to download say microsoft from the NY exchange and Barclays from the London exchange etc. I thought it would be more fun to make some dummy data.

SeedRandom[1];
days1 = Union@RandomInteger[{1, 365}, {400}];
SeedRandom[2];
days2 = Union@RandomInteger[{1, 365}, {400}];

in the next lines you could use DateList instead of AbsoluteTime but as above I think it is best to stick with absolute times.

list1 = {AbsoluteTime[#], RandomReal[{1, 30}]} & /@ Thread[{2012, 1, days1}]
list2 = {AbsoluteTime[#], RandomReal[{1, 30}]} & /@ Thread[{2012, 1, days2}]

Timing[
 new1 = newList[list1, list2];
 new2 = newList[list2, list1];
 ]

(* {0.006115, Null} *)

new1[[All, 1]] == new2[[All, 1]]
(* True *)

this was on a 2006 mac mini running 10.6.8 with Mma 8.0.4

---

Edit

The test data offered by the OP is flawed because it does not enable the specifications to be satisfied. So we can either use a test data set as above or trim the OPs test data. Timing again is on an old 2006 mac mini.

list1 = FinancialData["PETR4.SA", {"2007"}];
list2 = FinancialData["XOM", {"2007"}];

As above the first list has to have its first element removed for the OPs specifications to work with this data.

Timing[
new1 = newList[Rest@list1, list2];
 new2 = newList[list2, Rest@list1];
]
(* {0.001859, Null}*) 

new1[[All, 1]] == new2[[All, 1]]
(* True *)

Dimensions[new1]
(* {256, 2} *)

Answer 5

I looked for a solution without using Position[]

list1 = {{{2007, 1, 2}, 12.04}, {{2007, 1, 4}, 11.51}, {{2007, 1, 6},  12.14}};
list2 = {{{2007, 1, 2}, 20.91}, {{2007, 1, 3}, 24.34}, {{2007, 1, 4},  24.67}, 
         {{2007, 1, 5}, 12.14}, {{2007, 1, 6}, 20.97}};

f[list_, x_] := Join @@ {#[[1]], {{x, #[[1, -1, 2]]}}, #[[2]]} &@
                         GatherBy[list, Less @@ AbsoluteTime /@ {#[[1]], x} &]

{newL1, newL2} = Fold[f[#1, #2] &, #[[1]], Complement[#[[2]][[All, 1]], #[[1]][[All, 1]]]] 
                      & /@  Permutations@{list1, list2}

still doesn't handle the corner case (when the missing value is last one)