Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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

share|improve this question
    
Related: mathematica.stackexchange.com/q/11746/121 –  Mr.Wizard Jan 14 '13 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 Jan 14 '13 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. –  Mr.Wizard Jan 14 '13 at 10:36
    
@Wizard, I edited my original post with real data, so you can better understand the situation. –  Rod Jan 14 '13 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. –  Mr.Wizard Jan 14 '13 at 10:57

5 Answers 5

up vote 1 down vote accepted

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}]}}
 ]
share|improve this answer
    
@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 Jan 14 '13 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 Jan 14 '13 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? –  Mr.Wizard Jan 14 '13 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 Jan 14 '13 at 12:43
1  
@Rod Done. The third argument is optional; if it is not given Missing[] will be used. –  Mr.Wizard Jan 14 '13 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}}
}
*)
share|improve this answer
    
Yes, unfortunately there is a discrepancy in dates in the first position... list1 begins on Jan 2nd, while list2 begins on Jan 3rd... –  Rod Jan 14 '13 at 4:32
    
Looks to me like they each begin on January 2. –  David Carraher Jan 14 '13 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 Jan 14 '13 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) –  belisarius Jan 14 '13 at 7:46
1  
@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? –  Mike Honeychurch Jan 14 '13 at 11:52

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} *)
share|improve this answer
    
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 Jan 14 '13 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. –  Mike Honeychurch Jan 14 '13 at 11:34
1  
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. –  Mike Honeychurch Jan 14 '13 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)

share|improve this answer
    
After running your code I got two lists, both of different Dimensions[]. –  Rod Jan 14 '13 at 10:59
    
@RodLm I had included only the new list1 code. Now it's complete. Try it again, please –  belisarius Jan 14 '13 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 Jan 14 '13 at 12:58

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}};
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.