Find minimum difference in arrays from exact value

Hi!

I have option strike prices (Strike), date, and price index correspond to date.

I need to find the strike value (from Strike column) which is the nearest to Price for correspond day.

For example: The nearest price of 5848 for 05.02.2016 date is 5850.

The nearest price of 5689 for 08.02.2016 date is 5675.

The data is attached in excel file.

I should have only one Strike (column 1) price which is the nearest to the Price (column 3) for each date.

One simple table in the output, such as:

05.02 - one price,

08.02 - one price,

09.02- one price and so on.

Can you help me please how to solve this in MATLAB?

Thanks a lot

[YT]

What have you tried yourself already (please provide some code)? It's not a difficult problem, so are stuck on how to solve it or do you have trouble with coding it yourself?

[Artem Bielykh]

I tried to do it in Excel, and it does not work very good - a lot of hand job.

I have no code, and have no idea how to code this solution in Matblab.

Answer 1

[YT]

I've created this snippet for you with comments.

clear all;
close all;

%read in your xlsx file
T = readtable('Optionprices.xlsx');

%get unique dates
dates = unique(T.Date);

%loop over dates
for i = 1:numel(dates)
    %get the indices for rows of current date
    idx = ismember(T.Date, {datestr(dates(i))});
    currentTable = T(idx,:);
    
    %the amount of rows for each one
    aoe = size(currentTable,1);
    %get the absolute difference between price and strike and paste it into the field
    for j = 1:aoe
       currentTable.NearestPrice(j) = abs(currentTable.Price(j) - currentTable.Strike(j));
    end
    
    %get the smallest value for the difference and return index
    [~,I] = min(currentTable.NearestPrice);
    
    %create vector of zeros 
    vector = zeros(aoe,1); 
    %put in the strike value at the found index
    vector(I) = currentTable.Strike(I);
    currentTable.NearestPrice = vector;
    
    %replace the current values in your old table
    T(idx,:) = currentTable;
end

%save the table under new name
writetable(T,'OptionpricesV2.xlsx');

Because you're new to all this, I suggest reading some of the documentation and try to make your own snippets. The people here are glad to help but next time you should also provide a bit of code of what you've already tried.

[Artem Bielykh]

Thank you very much!

Answer 2

[Image Analyst]

Subtract and then use min():

priceDifferences = strikePrice - dailyPrice;
[minDiff, indexOfMin] = min(priceDifferences);
strikeValue = strikePrice(indexOfMin)