# Handling holidays in financial time series

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[].

• Commented Jan 14, 2013 at 10:02
• @Wizard I have seen this post. However it considers only one time series and fills the missing values with zeros, and not previous values.
– Rod
Commented Jan 14, 2013 at 10:19
• Yes, I added an answer below that extends my method to fill with previous values. Beyond filling the gaps, what do you lack to compare the two? I'll try to answer it if I understand. Commented Jan 14, 2013 at 10:36
• @Wizard, I edited my original post with real data, so you can better understand the situation.
– Rod
Commented Jan 14, 2013 at 10:52
• So it is not acceptable to fill all days (which with give a length of 361 for list1 in the update)? You must fill only days present in one list or the other? That can be done by pre-processing with methods described in mathematica.stackexchange.com/q/15425/121 -- if you need help let me know. Commented Jan 14, 2013 at 10:57

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}]}}
]

• @Wizard, I see here 2 problems: 1) In the first line, instead of 20.46 the correct value should be 67.02 (FinancialData["XOM", {"2006,12,29"}]); 2) Is it possible to obtain two separate lists instead of a joined one?
– Rod
Commented Jan 14, 2013 at 11:42
• As discussed above, I should have informed the first price value for list2 (XOM, in this case). Your code works perfectly...
– Rod
Commented Jan 14, 2013 at 12:03
• @Rod Glad I could help, and thanks for the Accept. I can add a parameter that will be used for the default fill value of either list if needed. Will that help you? Commented Jan 14, 2013 at 12:25
• Yes @Wizard! This would be very helpful! It would also be interesting to have 2 different lists as sepparate outputs, for instance, newlist1 and newlist2.
– Rod
Commented Jan 14, 2013 at 12:43
• @Rod Done. The third argument is optional; if it is not given Missing[] will be used. Commented Jan 14, 2013 at 12:49

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}}
}
*)

• Yes, unfortunately there is a discrepancy in dates in the first position... list1 begins on Jan 2nd, while list2 begins on Jan 3rd...
– Rod
Commented Jan 14, 2013 at 4:32
• Looks to me like they each begin on January 2. Commented Jan 14, 2013 at 5:01
• It was only my example... I can post both lists here, but my intention was to show the problem itself. Sorry for the confusion...
– Rod
Commented Jan 14, 2013 at 5:09
• @RodLm If your lists don't start on the same day your problem is underdefined because you asked to fill the price "from the previous day" (and there isn't one) Commented Jan 14, 2013 at 7:46
• @RodLm this then requires the program to download the previous data point. There are limits to what can be provided I think. code offered here will do what you need. it is up to you to construct a data set that is viable isn't it? Commented Jan 14, 2013 at 11:52

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}};


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]]];


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]]];
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} *)

• It's almost working... It works (partially) for newlist1 (which now has Dimensions[] of {257, 2}), but I get an error message when trying to create newlist2.
– Rod
Commented Jan 14, 2013 at 11:10
• Did you want me to guess what the error message was :). Since I do not know the error I cannot attend to it right now. I am going to bed now but will look at it tomorrow. Maybe you can show timings for various methods with you data set. Commented Jan 14, 2013 at 11:34
• Actually we know why the error exists and it has been pointed out a few times in comments. By definition if you specify that you must take a previous value, yet the FIRST element of your new list is a substituted date, there is no previous value, hence your specifications do not work with the data set you have chosen. Anyway if you choose data that conforms to your specifications you will have more luck. i am off to bed. Commented Jan 14, 2013 at 11:40

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)

• After running your code I got two lists, both of different Dimensions[].
– Rod
Commented Jan 14, 2013 at 10:59
• @RodLm I had included only the new list1 code. Now it's complete. Try it again, please Commented Jan 14, 2013 at 12:52
• I tried your new code, but I'm somehow getting some error messages like "Part::partw: Part 2 of #1 does not exist. >> "..
– Rod
Commented Jan 14, 2013 at 12:58