loop through a matrix and display the number of elements until the same value occcurs based on two columns of the matrix

below you find the data I am reffering to

K PC Price T date r moneyness

1160 1 111.95 0.052055 734506 0.0026425 1.09512931

1165 1 107 0.052055 734506 0.0026425 1.090429185

1170 1 102.1 0.052055 734506 0.0026425 1.085769231

1175 1 97.35 0.052055 734506 0.0026425 1.081148936

1180 1 92.45 0.052055 734506 0.0026425 1.076567797

1185 1 87.6 0.052055 734506 0.0026425 1.072025316

1190 1 82.75 0.052055 734506 0.0026425 1.067521008

1195 1 78 0.052055 734506 0.0026425 1.063054393

1200 1 73.2 0.052055 734506 0.0026425 1.058625

1205 1 68.45 0.052055 734506 0.0026425 1.054232365

1210 1 63.8 0.052055 734506 0.0026425 1.049876033

1215 1 59.15 0.052055 734506 0.0026425 1.045555556

1220 1 54.6 0.052055 734506 0.0026425 1.041270492

1225 1 49.75 0.052055 734506 0.0026425 1.037020408

1230 1 45.7 0.052055 734506 0.0026425 1.032804878

1235 1 41.4 0.052055 734506 0.0026425 1.028623482

1240 1 37.2 0.052055 734506 0.0026425 1.024475806

1245 1 33.15 0.052055 734506 0.0026425 1.020361446

1250 1 29.3 0.052055 734506 0.0026425 1.01628

1255 1 25.55 0.052055 734506 0.0026425 1.012231076

1260 1 22.05 0.052055 734506 0.0026425 1.008214286

1265 1 18.8 0.052055 734506 0.0026425 1.004229249

1270 1 15.75 0.052055 734506 0.0026425 1.000275591

1275 1 13.05 0.052055 734506 0.0026425 0.996352941

1280 1 10.65 0.052055 734506 0.0026425 0.992460938

1285 1 8.55 0.052055 734506 0.0026425 0.988599222

1200 1 70.6 0.049315 734507 0.0026411 1.05624975

1205 1 65.7 0.049315 734507 0.0026411 1.051866971

1210 1 61.25 0.049315 734507 0.0026411 1.047520413

1215 1 56.55 0.049315 734507 0.0026411 1.04320963

1220 1 52.15 0.049315 734507 0.0026411 1.03893418

1225 1 47.55 0.049315 734507 0.0026411 1.034693633

1230 1 43.4 0.049315 734507 0.0026411 1.030487561

1235 1 39.05 0.049315 734507 0.0026411 1.026315547

1240 1 34.85 0.049315 734507 0.0026411 1.022177177

1245 1 31.2 0.049315 734507 0.0026411 1.018072048

1250 1 27.1 0.049315 734507 0.0026411 1.01399976

1255 1 23.75 0.049315 734507 0.0026411 1.00995992

1260 1 20.3 0.049315 734507 0.0026411 1.005952143

1265 1 16.9 0.049315 734507 0.0026411 1.001976047

1270 1 13.4 0.049315 734507 0.0026411 0.99803126

1275 1 11.4 0.049315 734507 0.0026411 0.994117412

1280 1 9.25 0.049315 734507 0.0026411 0.990234141

1285 1 7.2 0.049315 734507 0.0026411 0.986381089

1290 1 5.8 0.049315 734507 0.0026411 0.982557907

1295 1 4.25 0.049315 734507 0.0026411 0.978764247

1300 1 3.3 0.049315 734507 0.0026411 0.974999769

1305 1 2.5 0.049315 734507 0.0026411 0.971264138

1310 1 1.875 0.049315 734507 0.0026411 0.967557023

1315 1 1.15 0.049315 734507 0.0026411 0.963878099

1320 1 1.05 0.049315 734507 0.0026411 0.960227045

1325 1 0.65 0.049315 734507 0.0026411 0.956603547

1330 1 0.55 0.049315 734507 0.0026411 0.953007293

1155 1 116.9 0.12603 734507 0.0029711 1.095887706

1160 1 112.3 0.12603 734507 0.0029711 1.091164052

1165 1 107.55 0.12603 734507 0.0029711 1.086480944

1170 1 103.1 0.12603 734507 0.0029711 1.081837863

1175 1 98.35 0.12603 734507 0.0029711 1.077234298

1180 1 93.95 0.12603 734507 0.0029711 1.072669746

1185 1 89.6 0.12603 734507 0.0029711 1.068143713

1190 1 85.2 0.12603 734507 0.0029711 1.063655714

1195 1 81 0.12603 734507 0.0029711 1.059205272

1200 1 76.6 0.12603 734507 0.0029711 1.054791917

1205 1 72.35 0.12603 734507 0.0029711 1.050415187

1210 1 68.4 0.12603 734507 0.0029711 1.046074628

1215 1 64.55 0.12603 734507 0.0029711 1.041769794

1220 1 60 0.12603 734507 0.0029711 1.037500246

1225 1 56.75 0.12603 734507 0.0029711 1.033265551

1230 1 52.2 0.12603 734507 0.0029711 1.029065285

1235 1 48.6 0.12603 734507 0.0029711 1.024899028

1240 1 45.15 0.12603 734507 0.0029711 1.020766371

1245 1 41.75 0.12603 734507 0.0029711 1.016666908

1250 1 38.65 0.12603 734507 0.0029711 1.01260024

1255 1 34.95 0.12603 734507 0.0029711 1.008565976

1260 1 32.05 0.12603 734507 0.0029711 1.00456373

1265 1 28.7 0.12603 734507 0.0029711 1.000593123

1270 1 25.4 0.12603 734507 0.0029711 0.99665378

1275 1 23.35 0.12603 734507 0.0029711 0.992745333

1280 1 20.4 0.12603 734507 0.0029711 0.988867422

1285 1 18.1 0.12603 734507 0.0029711 0.985019689

1290 1 16.55 0.12603 734507 0.0029711 0.981201783

1295 1 14.1 0.12603 734507 0.0029711 0.977413359

1300 1 12 0.12603 734507 0.0029711 0.973654077

I need to find the element where the first column on the left (K) is the same and additionally the data is obtained from the next date (for example the first date is 734506 and K= 1200, so I am looking for the row in which K=1200 occcurs at day 734507 and so on). For K=1200 on day 734507 I need to row of K=1200 on day 734507. From what I can see it's not possible to compute that based on the two criteria date and K, because for one date there are several K's, but it should be possible for the time to maturity (T). T is computed usind numbers of days outstanding/366 and the difference between T of two options between two weekdays is 1/366. So what I am looking for is a way how I can find for the same strike (K) the option which has one day less time to maturity T (T(1)= T(0)-1/366) the difficulties is, that in the sample there are just trading days that means it's possible that between two datapoints there are three days and no just one. are there ways to do that? at least to know how to get the number of the row in which the next value with the same strike and another criteria which is the same would help me a lot.

thanks!

Answer 1

[Cedric]

Assuming this array is stored in variable data, if you wanted to extract all entries where K = 1220, you would do it this way:

 >> data(data(:,1)==1200,:)
 ans =
   1.0e+05 *
    0.0120    0.0000    0.0007    0.0000    7.3451    0.0000    0.0000
    0.0120    0.0000    0.0007    0.0000    7.3451    0.0000    0.0000
    0.0120    0.0000    0.0008    0.0000    7.3451    0.0000    0.0000

If you wanted just prices (column 3), you would get them with

 >> data(data(:,1)==1200,3)
 ans =
      73.2000
      70.6000
      76.6000

If you wanted the date (column 5), this would be

 >> data(data(:,1)==1200,5)
 ans =
      734506
      734507
      734507

So there is no need to use both K and date/time to extract relevant data, unless you really need to know/process data/time specifically.

EDIT: this solution is providing all entries that have same K as the one that you are interested in. Based on the same principle, here is a function that provides you with the next entry with same K as the K found on a row that you specify:

 >> findNext = @(rId) data(rId + find(data(rId+1:end,1) == data(rId,1), 1),:) ;

 >> findNext(9)
 ans =
   1.0e+05 *
    0.0120    0.0000    0.0007    0.0000    7.3451    0.0000    0.0000

which is the entry that you are interested in, meaning the next entry with K=1200 .. if I understand well.

Or simpler, if you just want the rowId:

 >> findNextRow = @(rId) rId + find(data(rId+1:end,1) == data(rId,1), 1) ;

 >> findNextRow(9)
 ans =
    27
 >> findNextRow(1)
 ans =
     55

However, are you sure that you want to treat your data row by row (take one, find next, find next, find next, ..) when it is likely that you could find a way to treat the whole set of entries in a vector, one-shot way?

[Locks]

thanks for your answer. However, I think I made mysself still not clear what exactly I am looking for. I need to compute the value of a portfolio, consisting of a long position in the underlying (this row is not in the data set above, but thats no so important) and a short position in the option. for each day there are several more than just one similar strike, due to the fact that options with the same strike can have other time to maturity and therefore it's not enough to find the next element with the same strike (K) but the next element with the same stike(K) and one day less time to maturity. this is why I intend to find the next element based on two critarias. my idea was to get a vector with the rownnumber of the next element so I can start computing the portfolio in t=0 (at the beginning), in t=1 (should be the ned element, as said before, same strike but one day less to maturity (4th column in the dataset above)) and compute the difference between the value of the portfolio at t=0 and at t=1

[Cedric]

You can add conditions and perform a logical AND (&) between all of them.

If the time difference is exactly 1/366 or 3/366, you can test e.g.

 rowId = find(data(:,1)==1200 & data(:,4)==0.052055-1/366, 1) ;
 if isempty(rowId)
    rowId = find(data(:,1)==1200 & data(:,4)==0.052055-3/366, 1) ;
 end

You could also test for a time range, e.g.

 t0 = 0.052055-1/366 ;
 rowId = find(data(:,1)==1200 & data(:,4)>2.9*t0 & data(:,4)<1.1*t0, 1) ;
 if isempty(rowId)
    rowId = find(data(:,1)==1200 & data(:,4)>2.9*t0 & data(:,4)<3.1*t0, 1) ;
 end

[Locks]

for what does the ,1 in "1/366,1" stand for?

if I do not want so search for strike== 1200 but start in with the first strike which is 1160 in the data set above and then loot through the whole first colum and for every new element in the first column look for the the next row where the strike equals this one and the option has one day/three day (depending on whether it's a weekend or not, or it's also possible that there is another number, in case there are public holidays in between so it should look if there is the same strike in the data which follows the next day and if there is an option with x/366 days less to maturity, when x is the difference between the date from the option we have started with and the option which we are looking for

[Cedric]

The 1 tells FIND to return only the first match. It should not be necessary, but I put it as an example (it is sometimes useful when you want to write code that manages only scalar outputs of FIND and you want to prevent from crashing if a pathological situation happens where FIND would return multiple occurrences).

My first solution was to extract all occurrences of a particular strike; the second was to add a condition testing for the time, with an additional mechanism that assumes week end gaps when nothing is found for the day after. Now it seems that you want to follow a strike by hoping from one entry to the next according to several possibilities for the size of the gap. Then you'll probably want to take other starting times for the same strike and do the same, and then you'll want to do that for all strikes.. my point here is that it looks like it will become awfully complicated with a lot of nested loops and mechanisms, and you should consider having a more segmented/structured approach. As an example, you could go for something like (keep in mind that I can't fully understand what you are doing, so it is an illustration):

 Split data by strike ID.

 For strike in array of strikes:
    While list of entries for this strike not empty:
       For 1st entry, extract all entries that are separated from its time
       by a multiple of 1/366 and store as a time series.

The output of such a process would be e.g. a struct array of strikes, and each entry of this struct array would contain e.g. a strike ID and a cell array of price time series.

[Locks]

I see your point, but unfortunately due to the fact that there are lots of strikes, several time to maturities for each dates with the same strike and in the end, I will have not only call options but also put options, I would have around 80 different matrices, that's why I prefer to get a more complicated code for just one (or maybe 2, one matrix for calls and one for puts). So I try to write in more detail, what I am looking for.

The matrix I mentioned at the beginning is calles matrix and on the left side of the data I pasted, there is addditonally a column consisting of the future prices. However, the imporatant things here are the strike price (first column above), the date (5th column) and the time to matuiry (4th column). In the end, I will calculate the hedging error of a delta hedged portfolio as follows:

Hedging error= delta(t)*Futures Price(t)+Bond(t)-call(t)

from the data I have, calculating those imputs is not such a great deal. At time zero (t=0) the portfolio is selfinancing, which means the hedging error is zero and Bond(0)=call(0)-delta(0)*Future(0)

The Bond price is adjusted each time as follows:

Bond(t)= e^(r*t) Bond(t-1) + Future(t)*(delta(t-1)-delta(t))

to do this computation, I need an additional array which gives me the row where I find the same option (same strike, and same time to maturiy, reduced by the numbers of day which have passed by.

The idea I had to do this was the following:

1) Looking for the next strike (K) is no big deal, this I am able to do now

2) for each row, the code should loop through the date column and stop as soon as the date has changed. As you can see in the example above, on day 734506 there are several options, but the corresponding one which same strike and one/two or three day (depending on weekends/ holidays) less to maturity will follow at day 734507. Then the code should calculate the difference between the two days (734507-734506) and look for the row where we find in coulmn 1 the same strike (K) and in column 4 (T) the time to maturity which corresponds to the T before minus the difference in days multiplied by the factor 1/366

Is there a way to do this?

[Cedric]

Ok, well I need to know whether you are comfortable with (or willing to spend a little time learning) anonymous functions, logical indexing, ARRAYFUN, BSXFUN, or if I should stick to more basic operations and loops.

For example, if I do

 >> idStrike = @(rId) data(:,1)==data(rId,1) & ...
       any(abs(bsxfun(@minus, data(:,4),(data(rId,4):-1/366:0))) < 1e-4, 2) ;
 >> id = idStrike(9) ;
 >> plot(data(id,5), data(id,6)) ;

telling you that..

• We define the anonymous function idStrike for identifying/following a given strike based on an initial row ID.
• The initial idea was to use ISMEMBER for testing if time is the starting time minus a multiple of 1/366, but this couldn't work because differences are not exactly multiples of 1/366.
• We use BSXFUN to return an array of differences between all times and the initial time minus all relevant multiples of 1/366. Testing if the absolute value of differences is below a certain threshold identifies entries that are close to the initial time minus multiples of 1/366.
• idStrike returns a vector of logicals identifying rows that have at the same time the same strike as the initial one, and a time that matches the test described above with BSXFUN.
• The vector of logicals returned by idStrike can be used for indexing data and extracting all relevant entries for a given initial row.

Is that completely out of reach for you or do you know roughly what I am doing?

[Locks]

At the moment,I have to admit that I have no idea what arrayfun, bsxfun etc. are doing, but I am willing to learn so I would suggest using those

could you give me a dataset for which I can run that code? the matrix I ve got is much to long and that way it doesn't really help me understand what is going on. do you have a good paper you could recommend to get a better understanding?

[Cedric]

Just run it on the chunk of data that you provided in the main question, at the top.

The first line of code defines a function idStrike(), the same way you would define a function in an m-file, but here I do it "on the fly" in the command window as an anonymous function; the advantage is almost only that I don't have to ask you to create an m-file. After executing this statement, you have a function that takes a row number rId as input and returns a vector of logicals that flag all rows of the dataset that correspond to the strike present at row rId.

Row 9, for example, is the first entry with strike 1200.

 >> id = idStrike(9) ;
 >> find(id)
 ans =
     9
    27

As you can see, it got rows 9 and 27 that correspond to the history of the strike present at row 9. Now you can use this id to index the dataset and get relevant columns.

 >> data(id,1)              % Strike IDs.
 ans =
        1200
        1200

 >> data(id,5)              % Dates.
 ans =
      734506
      734507

 >> data(id,4)              % Times.
 ans =
    0.0521
    0.0493

 >> data(id,6)              % Prices.
 ans =
    0.0026
    0.0026

[Cedric]

In response to your comment under the other answer,

 find(id)

is the vector that you mention. Let's discuss a simpler example.

 >> A = [7 6 6 7 7 8 7 6 8 7; 3 3 3 3 3 4 4 4 4 4; 0.1:0.1:1 ].'
 A =
    7.0000    3.0000    0.1000
    6.0000    3.0000    0.2000
    6.0000    3.0000    0.3000
    7.0000    3.0000    0.4000
    7.0000    3.0000    0.5000
    8.0000    4.0000    0.6000
    7.0000    4.0000    0.7000
    6.0000    4.0000    0.8000
    8.0000    4.0000    0.9000
    7.0000    4.0000    1.0000

If you want to find the location of all rows that have a 7 in the first column, you can do

 >> id = A(:,1) == 7
 id =
     1
     0
     0
     1
     1
     0
     1
     0
     0
     1
 >> class(id)
 ans =
     logical

As you can see, id is a vector of logicals (1=true), which 1's at rows matching rows of A where the 1st column is 7. Now you can get row numbers with FIND (and usually it's what people will do):

 >> row = find(id)                   % Vector of row numbers.
 row =
     1
     4
     5
     7
    10

but it is often not necessary as both row numbers and logicals can be used for indexing. The first is called subscript indexing and the second logical indexing. They are the same, as shown below where I use them to extract rows of A whose 1st column is 7:

 >> A(row,:)
 ans =
    7.0000    3.0000    0.1000
    7.0000    3.0000    0.4000
    7.0000    3.0000    0.5000
    7.0000    4.0000    0.7000
    7.0000    4.0000    1.0000

 >> A(id,:)
 ans =
    7.0000    3.0000    0.1000
    7.0000    3.0000    0.4000
    7.0000    3.0000    0.5000
    7.0000    4.0000    0.7000
    7.0000    4.0000    1.0000

The actually not completely the same, as you needed one more operation to define row than to define id. Additionally, knowing row numbers often setup people to developing bad approaches for treating data.

Now id is the outcome of a relational operation, and you can combin logically multiple of them when needed, e.g. for flagging all rows whose 1st column is 7 and in the same time whose 3rd column is above 0.4, we can do the following:

 >> id = A(:,1) == 7 & A(:,3) > 0.4
 id =
     0
     0
     0
     0
     1
     0
     1
     0
     0
     1

again, this can be used to extract relevant rows from A:

>> A(id,:) ans = 7.0000 3.0000 0.5000 7.0000 4.0000 0.7000 7.0000 4.0000 1.0000

If we wanted to get rows whose 1st column is 7 and whose 3rd column is a multiple of 0.5, knowing that values are in the range [0,5] for example, we could do

 >> id = A(:,1) == 7 & ismember(A(:,3), (0:0.5:5))
 >> A(id,:)
 ans =
    7.0000    3.0000    0.5000
    7.0000    4.0000    1.0000

Understanding perfectly what we do here is crucial for understanding what I've done in my answer. When I defined the function idStrike(), my first attempt was to get an id vector almost the same way:

 >> id = data(:,1) == K & ismember(data(:,4), (T:-1/366:0))

where K and T are the strike/time from whom you want to study the history. This would work if differences between relevant times were exactly multiples of 1/366, but they are not because of rounding errors.

I had therefore to find a way to test if a time belongs to small intervals around the initial time minus multiples of 1/366, which I do using ANY and BSXFUN.

You might not understand BSXFUN yet, but what you should understand is that this function idStrike() returns a vector of logicals whose elements are 1/true for rows that have simultaneously the same strike as the initial one, and a time that is close to the initial time minus a multiple of 1/366. And of you understand this, you know that you could get row numbers with find(id) as mentioned at the top of this post, but that might not be necessary.

[Locks]

I completely understand, what you're doing up to this formula:

>> id = data(:,1) == K & ismember(data(:,4), (T:-1/366:0))

here I do not really get what the "K" is is doing in the formula

the code you pasted above:

>> idStrike = @(rId) data(:,1)==data(rId,1) & ...
       any(abs(bsxfun(@minus, data(:,4),(data(rId,4):-1/366:0))) < 1e-4, 2) ;
 >> id = idStrike(9) ;
 >> plot(data(id,5), data(id,6)) ;

Doesn't really work for me, I get the following error message

Error: File: test3.m Line: 4 Column: 2 Unexpected MATLAB operator.

the script where I pasted your cose is calles test3.m

and what I still do not really get are those fancy elements, for example what is

@(rId) excatly doing?

or (@minus?

I tried to come up with a solution myself, but I am not sure if it's possible to do something like this is matlab. Could I take the first row and then look through the column with all dates until there is a change of dates, then take the difference between the first date and this new date and then us the & operation to look for the option with the same strike and the difference computed using the two dates, times 1/366?

[Cedric]

The error that you get is because you copy the >>, that I write to represent the prompt of the command window.

About the line

 >> id = data(:,1) == K & ismember(data(:,4), (T:-1/366:0))

does it help if I write parentheses?

 >> id = (data(:,1) == K) & (ismember(data(:,4), (T:-1/366:0)))

If not, you understood that

 data(:,1) == K

returns a vector of logicals that is true where elements of the first column are equal to whatever value is store in K and false otherwise. ISMEMBER returns also a vector of logical, whose elements are true where elements of the fourth column are in the set defined by its second argument. This set is the set of all positive numbers equal to T minus multiples of 1/366. So the whole expression generates a vector of logicals whose elements are true each time corresponding elements in both aforementioned vectors of logicals are true.

Look at the following example

 >> A = rand(5,2)            % Dataset with 5 rows.
 A =
    0.1576    0.1419
    0.9706    0.4218
    0.9572    0.9157
    0.4854    0.7922
    0.8003    0.9595

 >> A(:,1) > 0.5            % Spots rows where 1st column element is >0.5.
 ans =
     0
     1
     1
     0
     1

 >> A(:,2) < 0.5            % Spots rows where 2nd column element is <0.5.
 ans =
     1
     1
     0
     0
     0

Now if you want to spot rows where both conditions are true, you can perform a logical AND (&) of the two tests (here, I save the output in a variable that I name id, that I can later use for logical indexing):

 >> id = (A(:,1) > 0.5) & (A(:,2) < 0.5)
 id =
     0
     1
     0
     0
     0

Here you see that only row #2 si matched by the two conditions simultaneously..

Now if you wanted to extract rows of A that have both "element of 1st col >0.5" and "element of 2nd col <0.5", you could use id as a logical index:

 >> A(id,:)
 ans =
    0.9706    0.4218

Now play a bit with ISMEMBER if you don't understand it. What I do is that I provide a set of numbers to this function, that is the initial time, say T, minus all multiple of 1/366 until it reaches 0.

[Locks]

This step I got now, I tried the following:

K=1200
id = (matrix(:,2) == K) & (ismember(matrix(:,5), (T:-0.00274:0)))

That worked, except fro the fact that as soon there are rounding differences, it's not working anymore. Is is possible to adjust this formula in a way that is able to treat such small differences?

[Cedric]

Perfect, you reached a mandatory step for understanding what comes next. I first tried this solution that you just tried and realized what you just realized: it doesn't work because of rounding errors.

The second step was therefore to replace the test "is member of a set of numbers defined by the original time minus multiples of 1/366" by a more flexible test that checks whether time entries are within a certain range of all the initial time minus multiples of 1/366.

Now you could certainly build some function for doing that with one or two FOR loops in an M-file, but I proposed a more concise (a sadly less simple/clear) solution:

 any(abs(bsxfun(@minus, data(:,4),(data(rId,4):-1/366:0))) < 1e-4, 2) ;

This would replace the ismember(...) test, and if you run it you'll realize that it works where ismember was previously failing.

To understand it, you have to understand quite a few notions.. first, the @ builds a function handle (like a pointer to a function):

 >> f = @sin ;
 >> f(pi/2)
 ans =
     1

so, this is what you have to use when you want to pass a function to another function. Here, is was a handle on a named function: SIN. Now, a little more complicated, anonymous functions:

 >> f = @(t, x) sin(t) + x ;
 >> f(pi/2, 8)
 ans =
     9

here f is a handle on function with two input arguments but that has no name. The syntax is

 functionHandle = @(arg1, arg2, ..) functionBody

It allows us to declare functions "on the fly" in one line, without having to create an M-file, but with important limitations (no loop, no conditional statement, etc).

BSXFUN will apply a function with two input args, given through its handle as a first argument, to each pair of values from arrays given as second and third arguments. Here we pass and handle on MINUS, the vector of all times, a vector made of the initial time minus all multiples of 1/366 (down to 0), and BSXFUN return an array of differences (you'll have to try to see how it works). Now the only thing that is left is that we have to spot rows of this array that contain a number that is close enough to 0 (or below a certain small threshold in absolute value). When a row contain a value close enough to 0 in absolute value, it means that the time was close enough to the original time minus some multiple of 1/366 to be a match. Testing if a row contains an element that verifies some condition can be done using ANY.

You'll have to play with this for a moment I guess before it's clear to you. The good news is: there are a lot of fundamental MATLAB mechanisms in there, so it's worth the time investment.

[Locks]

I tired this:

K=1200
id = (matrix(:,2) == K) & any(abs(bsxfun(@minus, matrix(:,5),(matrix(rId,5):-1/366:0))) < 1e-4, 2) ;

But that's not working.

I get this error message:

Undefined function or variable 'rId'.

Error in find_30032013 (line 2) id = (matrix(:,2) == K) & any(abs(bsxfun(@minus, matrix(:,5),(matrix(rId,5):-1/366:0))) < 1e-4, 2) ;

What I am doing wrong?

[Cedric]

Simply that rId was the row ID in the initial example that I gave with the anonymous function, and you didn't define it. You implemented the condition

 matrix(:,2) == K

where K is 1200 (it seems that your column IDs are shifted by 1 now, so I will include this shift in what follows). In your initial question you asked for a solution in which you could specify a row and get the corresponding history. For this reason, I built the anonymous function so it takes a row ID and not a K. My test was therefore

 data(:,2) == data(rId,2)

In your case, you just realized that data(rId,2) was 1200 for the row that you were interested in, and saved this value into K.

So you can either do

 rId = 9 ;               % Row with 1st K=1200.
 id = (data(:,2) == data(rId,2)) & ...
       any(abs(bsxfun(@minus, data(:,5),(data(rId,5):-1/366:0))) < 1e-4, 2);

or

 K = data(9,2) ;               % 1200
 T = data(9,5) ;               % time for same row.
 id = (data(:,2) == K) & ...
       any(abs(bsxfun(@minus, data(:,5),(T:-1/366:0))) < 1e-4, 2) ;

[Locks]

that does almost work, but there is the problem, if there is an option with the same strike (K) and a mutiple of the time to maturity, an option with the same date can also be seleccted (I checked the sample and this is the case) so an additional criteria has to be added which says that the date must be not equal to the date from the last option, how to I do that?

something like this:

id = (matrix(:,2) == K) & any(abs(bsxfun(@minus, matrix(:,5),(T:-1/366:0))) < 1e-4, & (matrix(i,5)~=matrix(i+1,5)

Ho can I refere to the last value at which we looked?

an other way would be to define that the next time to maturity must additionally be smaller than the last one. now the following data are given a one in the logical vector:

1276.000 1200 1 77.500 0.0465 734508 0.00263 1.063333

1274.300 1200 1 74.900 0.02191 734509 0.0025 1.061916

1274.299 1200 1 75.600 0.04383 734509 0.0026 1.061916

the second row should ned be included, because that option has a complete other time to maturity

and by the way, thanks again for the support, I am really grateful for your patience

[Cedric]

You're welcome!

As far as I understood, rows with same strike and close enough to the initial time minus some multiple of 1/366 must belong to the same time series..? If there were another time series with same strike and overlapping the aforementioned initial time minus some multiple of 1/366, there would be no way to differentiate them even with the date. If you have a mix between two time series that should be different, it might mean that the threshold 1e-4 is too big. If I am wrong, what did I misunderstand? .. or is this phenomena that you mention happen in the data that you provided in the question? If so, could you tell me which rows are involved?

[Locks]

if you look at the data take the difference between the times to maturity you get:

0.0465-0.02191=0.0246 0.0465-0.04383=0.0027

and 9*0.0027 equals more or less 0.0246. this option here:

1274.300 1200 1 74.900 0.02191 734509 0.0025 1.061916

with time to maturity of 0.02191 should not be part of this series, it's from another series with shorter time to maturity

however, those two datasets here:

1276.000 1200 1 77.500 0.0465 734508 0.00263 1.063333

1274.299 1200 1 75.600 0.04383 734509 0.0026 1.061916

belong together. you can see that the Strike is the same (1200), the first one is from day 734508 and the second from day 734509

and the difference between time to maturity equals more or less 1/366

0.0465-0.04383

if there is a way to limit the multiple of 1/366 up to times 5 or something like this, I guess this would help.

or if there is a way to take this data here:

1276.000 1200 1 77.500 0.0465 734508 0.00263 1.063333

loop trough the column with the dates and look for a value which is not equal to 734508 (then you know that the new day has begun). than calculate the difference between 734508 and that new day, in our example it would be 734509-734509=1 and the run the part where you substract more or less 1/366 times this difference (here times 1)

[Cedric]

I don't have the same data as you, but using what is in you original question, here is what I can say.

There are three entries with K=1200. They have the following times:

    0.0521
    0.0493
    0.1260

Checking whether 0.0493 if in the time series associated with 0.0521, I find

 >> any(abs((0.0521:-1/366:0) - 0.0493) < 1e-4)
 ans =
     1

so yes, the second row with K=1200 is in the same time series as the first row. Now checking again for the third row with time=0.1260:

 >> any(abs((0.0521:-1/366:0) - 0.1260) < 1e-4)
 ans =
     0

I find that it is not in the same time series. Same if I test the time that you provided 0.02191:

 >> any(abs((0.0521:-1/366:0) - 0.02191) < 1e-4)
 ans =
     0

Now you provided two other entries that are together; the first has a time of 0.0465. If I check whether 0.02191 is part of this time series, I find

 >> any(abs((0.0465:-1/366:0) - 0.02191) < 1e-4)
 ans =
     1

which indicates that it does based on our test. If you tell me that it doesn't indeed, then it might mean that the threshold of 1e-4 is to large and that we should narrow it. Let's compute the difference between 0.0465 minus a certain multiple of 1/366, and 0.02191, that was below threshold:

 >> find(abs((0.0465:-1/366:0) - 0.02191) < 1e-4)
 ans =
    10

this means that 0.02191 is was close enough to 0.0465-(10-1)*1/366, and even that the difference was below 1e-4.. let's check

 >> 0.0465-(10-1)*1/366 - 0.02191
 ans =
  -1.6393e-07

yep, so one solution could be to set the threshold at 1e-9 for example, but this would be useless because your times are not given with this precision. Now I computed the same difference between two times that should be together and found ~8e-6 .. this is larger the the 1.6e-7 found above for times that should not be together.

The conclusion is either that you are wrong and their these times are together, or that it's not possible to separate time series that should not be together if the difference is too small.

We could limit to 5 days, but what if a second time series starts the day after close enough to a given time series.

If you wanted to limit tracking to 5 days anyway, you could do it almost the way you proposed:

 rId = 9 ;                                          % Row with 1st K=1200.
 id = (data(:,2) == data(rId,2)) & ...              % Condition on K.
      any(abs(bsxfun(@minus, data(:,5), ...
          (data(rId,5):-1/366:0))) < 1e-4, 2) & ... % Condition on time.
      (data(:,6) <= data(rId,6)+5) ;                % Condition on date.

[Locks]

for some reason, this doesn't work either. Is there a way I can send you a file with the data? I guess that would help a lot, I could explain you in more detail what is needed

and this part (data(:,6) <= data(rId,6)+5)

Is not what I ment, from what I can see, this menas that we look only at the next five days and that's not true, but it gave me an idea to improve the code. the time tu maturity gives us the numbers of years until maturity comes. for example if time to maturity is 0.2 and computed with 365 days/year, then there are 0.2*365 days left until maturity. in other words, istead of +5 I would likke to add

data(:,6)<=data(rld,6)+ data(rld,5)*365

and this rounded to the next number

this is solving another problem, I just discovered now, that there rows where I received "true" although the date (for example 734534) was far away from the expiration date

what I meant with the <= 5 is, that the next time to maturity value is not allowed to be smaller than 5*1/365

looking at the example I posted above

1276.000 1200 1 77.500 0.0465 734508 0.00263 1.063333

1274.299 1200 1 75.600 0.04383 734509 0.0026 1.061916

Here you see that there is one day in between (734509-734508) and the difference between the two times to maturity is 0.0465-0.04383=0.00267

1/366=0.00273224

the 0.00267 is equal to one day less to maturity (rounding problem occur, because I haven't pasted the exact value but deleted some numbers

when you look at the other data where I get a true

1276.000 1200 1 77.500 0.0465 734508 0.00263 1.063333

1274.300 1200 1 74.900 0.02191 734509 0.0025 1.061916

the first row is the same as above, the second row as one day less (734509-734508=1) but much less days to maturity

( 0.0465-0.02191=0.02459)

366*0.02459=8.97 so 9 days is the difference in time to maturity between the two values, although just one day passed (from 734508 to 734509). And this is why I thought that it would help if the change in time to maturity, multiplied with 366, is not allowed to be greater than 5

[Cedric]

I don't have time to read/process your comment now, but send me your file using the email address in my profile, and I'll come back on your comment and the file when I have a moment.

[Cedric]

I read anyway because I was curious and I understand better now.. you could try to build something around what follows (not tested)

 rId = 9 ;                                          % Row with 1st K=1200.
 id = (data(:,2) == data(rId,2)) & ...              % Condition on K.
      any(abs(bsxfun(@minus, data(:,5), ...
          (data(rId,5):-1/366:0))) < 1e-4, 2) ;     % Condition on time.

 buffer = data(id,:) ;

 % Post-process to eliminate rows with non-matching date.
 id = buffer(:,6) - data(rId,6) - round((data(rId,5)-buffer(:,5))*366) < 1 ;
 result = buffer(id,:)

What we do in the second part is to compare all dates with the initial date + relevant number of multiples of 1/366. If you evaluate

 round((data(rId,5)-buffer(:,5))*366)

you'll see that it gives the number of days between the initial time and times for all rows in buffer.

[Locks]

what could work is the following:

rowId = find(data(:,2)==1200 & data(:,5)==data(:,5)-1/366, 1) ;
if isempty(rowId)
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-2/366, 1) ;
 end
 if isempty(rowId)
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-3/366, 1) ;
 end
 if isempty(rowId)
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-4/366, 1) ;
 if isempty(rowId)
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-5/366, 1) ;   
 end
 if isempty(rowId)
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-1/365, 1) ;
 end
 if isempty(rowId)
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-2/365, 1) ;
 end
 if isempty(rowId)
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-3/365, 1) ;
 end
 if isempty(rowId)
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-4/365, 1) ;
 end
 if isempty(rowId)
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-5/365, 1) ;
 end

is it possible to do that with this for-loop:

rowId = find(data(:,2)==1200 & data(:,5)==data(:,5)-1/366, 1) ;
if isempty(rowId)

      for i=2:5
      rowId = find(data(:,2)==1200 & data(:,5)==0.052055-i/366, 1) ;
      end

end
 if isempty(rowId)
     for i=1:5
    rowId = find(data(:,2)==1200 & data(:,5)==0.052055-i/365, 1) ;
     end
 end

what is missing, respectively what I haven't been able to do is the following:

- round that part here: 0.052055-i/365, so that if there are other numbers due to rounding errors, the code is still working -instad of using 0.052055 as the initial time to maturity, how can I change the code that it's always taking the last time to maturity value?

[Cedric]

Ok, I had finally 10 minutes to spend on this. I just understood that we are not looking for times that are equal to the initial time minus some multiple of 1/366, but that the multiple has to be exactly the date difference! Sorry if it's what you were trying to explain from the beginning, I didn't get it. So this can be exploited not to filter out invalid rows afterwards, but for building a simpler condition on time/date.

I built the function

 function history = geHistory_byRowId(data, rId)
    dateDiff = data(:,6) - data(rId,6) ;    
    id = (data(:,2) == data(rId,2)) & ...          % Condition on K.
         (abs(data(rId,5)-dateDiff/366-data(:,5)) ...
            < 1e-4) ;                              % Condition on time/date.
    history = data(id,:) ;
 end

This time, I first build a vector of date differences, which is this "number of multiples of 1/366" we are looking for for identifying valid rows. In the test, we just look whether the initial time minus the date difference (# of days) times 1/366 is close enough to the time to maturity. Based on your data, if I execute

 >> data = matrix ; 
 >> data(:,1) = 1:size(data,1) ;    % Replace col. 1 with row ID, to
                                    % facilitate verifications.
 >> rId = 9 ;
 >> history = geHistory_byRowId(data, rId) ;
 >> history(:,1)
 ans =
           9
         196
         391
         603
         795
         988
        1181

The differences with what you wrote is 603 instead of 604. If I look at row 604, the strike is 1205 so I guess that it was a typo.

[Locks]

you're absolutely right, it should be 603 and the result is looking awesome

I am just not sure how to paste that in a script. I created a function names history and pasted this there:

function history = geHistory_byRowId(data, rId) dateDiff = data(:,6) - data(rId,6) ; id = (data(:,2) == data(rId,2)) & ... % Condition on K. (abs(data(rId,5)-dateDiff/366-data(:,5)) ... < 1e-4) ; % Condition on time/date. history = data(id,:) ; end

then I created a script pasting:

 data = matrix ; 
 >> data(:,1) = 1:size(data,1) ;    % Replace col. 1 with row ID, to
                                    % facilitate verifications.
 >> rId = 9 ;
 >> history = geHistory_byRowId(data, rId) ;
 >> history(:,1)

but that's not working

[Cedric]

The M file with the function has to be in the current directory .. or let's say that you have to set the current directory to be the one where the M-file is..

When you create the script, remove the >> (that I put to symbolize the prompt of the command window).

This script assumes that matrix exists in memory, which is the case right after you load the MAT file that you sent to me.

If none of this helps, please copy/paste the error message that you get here as a comment.

[Locks]

I had to save and close matlab and when I restarted it, it worked

I tried the code now for rId = 8 and there I can see that there are still some problems. If you run the code like this it will display the following:

               8
             390
             602
             794
             987
            1180

but the data from 390 onwards is not the same series. Is there a way the calculation stops, if there is no data matching on the next day?

additionally the final outcome should be a vectore where I can see the row number of the next option which matches the one I am looking at

is that possible doing something like:

    for i=1:length(F)   %F is the first column of the dataset
    data(:,1) = 1:size(data,1) ;    % Replace col. 1 with row ID, to
                                          % facilitate verifications.
       rId = 8 ;
       history = geHistory_byRowId(data, rId) ;
       history(:,1)
       end

by the way, I changes the name of the matrix called matrix into data, so have the same names

[Cedric]

As far as I can tell, there is no way to separate these series. Where is the end of the series starting at row 8 actually?

The issue is the following:

 >> n = data(390,6) - data(8,6)
 n =
     2

this shows that there are 2 days between dates in rows 8 and 390. Now if I compute time at row 8 minus 2/366 minus times at row 390..

 >> data(8,5)-n/366-data(390,5)
 ans =
   1.5519e-05

which is below the threshold of 1e-4, so it's considered as a match. The option to reduce the threshold is not available, as these differences computed for the group starting on row 9 peaks at ~6e-5 (which is precisely why I took the 1e-4 threshold). This means that a valid difference for initial strike at row 9 is larger than an invalid difference for initial strike at row 8.

So you need another approach/condition/data. Is there really no group ID coming from your source of data?

[Locks]

I have received this data and I need to check if there is an id or not, but I guess there isn't

the end of the series is the last line, in total there must be probably around 1000 different series and the data I sent you is just a smaller data set. In total I have 150'000 rows for each year.

there is no way to check ist the dataset for the next day? so for example when we are at day 734506, we just check whether at day 734507 (day which is not equal to the previous) there is an option with the same conditions?

[Cedric]

Could you illustrate how you apply this on the case that we just discussed with row 8 and row 390 being different series? If you can do it looking at the data that we have, we can certainly implement it.

[Locks]

Î am not that familiar with matlab to be able to write the code, but I am thinking about something similar to this:

take one row, for example row 8:

8 1195 1 78 0.052055 734506 0.0026425 1.06305

then loop through column 6 and stop as soon as the value ~= 734506

store this value

and then search if there is any value based on

rowId = find(data(:,2)==1200 & data(:,6)=the value stored for the date which is not equal the last one & data(:,5)==data(:,5)-1/366, 1), or similar to this

I am not sure if that possible to implement but I guess it would help if we could store the first date which is not equal to the date we are looking at in row 8

[Cedric]

It seems to me that it is just the non-vector way to do what we do in a vector way.

What made you say that data on rows 8 and 390 don't belong to the same series? You didn't follow the algorithm that you just described to reach this conclusion I guess, so how did you proceed?

If we follow your algorithm, the first step will be

 >> find(data(:,6) ~= data(8,6), 1)
 ans =
     196

so 196 is the first row with a date different from the date on row 8. Check:

 >> [data(8,6), data(195,6), data(196,6)]
 ans =
      734506      734506      734507

ok. Then we look for the next row that satisfies the three conditions.. but there is no value with date=734507, so we repeat the operation and hit the next date..

 >> find(data(:,6) == 734507+1, 1)
 ans =
     385

Check:

 >> [data(384,6), data(385,6)]
 ans =
      734507      734508

ok. Then we check for your three conditions

 >> n = 734508 - data(8,6) ;     % n equals 2 in this case.
 >> rowId = find(data(:,2)==data(8,2) & data(:,6)==734508 & ...
                data(:,5)==data(8,5)-n/366, 1)
 rowId =
       Empty matrix: 0-by-1

and we find nothing, BUT this test would fail detecting rows from the series starting at row 9 because the difference is never a multiple of 1/366 due to rounding error.. so we must use some threshold, and if we do so..

 >> rowId = find(data(:,2)==data(8,2) & data(:,6)==734508 & ...
                 abs(data(8,5)-n/366-data(:,5))<1e-4 , 1)
 rowId =
       390

we find 390!

So we are back at the original question, how do you know that row 8 and row 390 are not from the same series.

[Locks]

you're right, it is the same series, just one dday is missing, but the thing is that in the end, I need to compute de daily hedging error of a hedged portfolio and therefore just series where I have data for all the observed days is helpful to me.

that's why I said it's another series, because I have to treat it as another series and that's not just for the series starting in row 8, there are criteries such as the moneyness of an option which could lead to not include the option data of one specific day, because the option is too much in the money or out of the money and if the price changes and the option is not that much in the money or out of the money, it's possible that the series is back in my data

so what made me say that it's not the same it's just that I pasted the whole dataset n excel and filtered it and there I saw that one day is missing and as soon as one day is misssing (not the weekends or a holiday, because those data is missing for all series but a day where the other series do have data) I have to treat it as another series

[Cedric]

I performed a few tests and I think that the time difference between consecutive days is likely to be 1/365 and not 1/366(?) Using 1/365 brings differences that we compute from [6e-5, 2e-6] down to [6e-7,8e-8].

This "I realized that one day was missing" is difficult to implement if you had to check that it was not a holiday. If you wanted to break series if the time difference is neither 1 nor 3 days, it would not be too complicated, but it would be screwed by holidays unless you have a vector that indicates breaks on which you can synchronize.

In any case, the data treatment, check, and display, is getting a little more challenging now because of these particular cases, and I think that it would be a good time investment on your side, if you want to use MATLAB, to increase your skills so you can quickly test strategies.

[Locks]

if 1/366 or 1/365 is correct depends on the year we are looking at. for this series, 1/365 is better, but I ned to rund this on several years and there are years where 1/366 is better and that's why I would like to include both if that's possible

Just for my understanding, what we are doing now is to search in the data from the point where de date changes till the end, if a row matches the criterias we have implemented, is that correct?

Would it then not be possible to stop running this criterias as soon as the data have changes again?

for example, we are at day 734506, then we are going to start looking at the first point where column 6 is not equal to 734506 (for example 734507) and we stop the calculation as soon as the value in column 6 is not equal to 734507

do you have a good webside or something where I can learn those skills? the material I have is more general and basic stuff and if you have something that would really help me

[Cedric]

You can add a third input parameter to the function that I proposed, that would allow you to pass the 1/365 or 1/366 factor to the function.

To understand what we are doing, you have to play with simpler cases, e.g a matrix

 >> A = randi(8, 10, 3) ;  A(:,3) = A(:,3) + rand(10, 1)-0.5
 A =
    8.0000    6.0000    3.0085
    8.0000    6.0000    6.0108
    5.0000    6.0000    7.3176
    1.0000    4.0000    1.2948
    2.0000    5.0000    8.1443
    3.0000    3.0000    6.8786
    7.0000    6.0000    4.3116
    1.0000    2.0000    4.0328
    1.0000    6.0000    3.8507
    2.0000    2.0000    3.4390

and try to extract rows based on e.g. values from the first and second columns, using conditional operators and logical indexing. Then you can try a little more complicated, and include conditions on the 3rd column (not integer). Example

 >> id = A(:,1) > 6
 id =
     1
     1
     0
     0
     0
     0
     1
     0
     0
     0

what does this mean, what can you do with that, indexing? counting? other? what happens when we evaluate A(id,1) or A(id,2) or A(id,3) or or A(id,:)? do we have all rows at once or do we have to loop?

I don't understand what you want to do with " we stop the calculation as soon as the value in column 6 is not equal to 734507". As far as I understood, most time series were across multiple days ..?

About tutorials, there are plenty available online. Whichever you take, what you want at this stage is to improve your knowledge about matrix/vector indexing (subscript, linear, logical) and conditional operators. You already have a goal with your project, which is a great driver; you can quite easily understand plot options, integration, interpolation, etc, if you have the mathematical background, but my guess is that at this point what slows you down is data manipulation. That's why I mention indexing. There would also be knowing differences between arrays, cell arrays, structs, struct arrays, datasets, and how to index/use them, how to reshape/cat arrays, and how to perform operations on each element of data structures (arrayfun, cellfun, structfun). You could also learn about function handles, and anonymous functions. I gave you the keywords, now it's up to you to find information. A good source is http://www.mathworks.com/help/pdf_doc/allpdf.html in MATLAB's section.. these aren't tutorials per se, but there is a lot of information in these PDFs (e.g Primer, Mathematics, Data Analysis, Programming).

[Locks]

a colllegue of mine was able to help me, based on the code you provided me and it seems as it's working now. however, thanks a lot for your time and your input, it was really usefull and I will have a look at those information

[Cedric]

You're welcome! Please accept the answer if my original answer + 19 quite long comments helped.

Answer 2

[Locks]

sorry, I think I didn't make myself quite clear because all startet with another question here in the forum.

I am looking for a vector where I can see the number of the next element which corresponds to the one in the same line in the matrix I entered above.

for example when looking at the 9th line:

1200 1 73.2 0.052055 734506 0.0026425 1.0586250

I am looking for the rownumber of the next element with 1) K (first column)=1200 and T(4th colum) = 0.052055-1/366

if there are weekends, there will be three days in between that would mean: K (first column)=1200 and T(4th colum) = 0.052055-3/366

I hope this way it's clearer what I am looking for

[Cedric]

See the EDIT in my answer above.

[Locks]

I can see to plot, but in the end we're looking for a vector which tells us, in which row the next option with the same properties (same strike, call/put, and same time to maturity, except one/two/three days less) and this code doesn't give us that or am I mistaken?

[Cedric]

See my comment below the answer above.

[Locks]

I sent you the data. for K=1200 (the option in the 9th row, the following rows contains the same option: row 196 (day 734507) row 391 (day 734508) row 604 (day 734509) row 795 (day 734510) row 988 (day 734513 -> probably weekends in between) row 1181 (day 734514)

as far as I can see, thats it

time to matruity at day 734514 is 0.030137, which corresponds to 11 days

the command you send me does give me just 20 numbers as an output, but I also try to work out a solution with the information you provided to me so far