Help: Matlab code to import stock price data from an excel file using the function input.

I have an excel file named Stocks.xlsx which contains the closing prices of 87 S&P 500 stocks for the period 31/12/2007-30/11/2017. I have to choose four of them (e.g. ‘AAPL’, ‘AMZN, ‘HPQ’ ,’IBM’) and insert the prices into a script file for the period 29/11/2013-30/11/2017 using the function input. Can anyone help me write the matlab code. Thanks in advance.

This is what i wrote, but i have not been very successful.

m1=input('Insert the first stock: '); % We insert the ticker of the first stock in ‘’
m2=input('Insert the second stock: '); % We insert the ticker of the second stock in ‘’
m3=input('Insert the third stock: '); % We insert the ticker of the third stock in ‘’
m4=input('Insert the fourth stock: '); % We insert the ticker of the fourth stock in ‘’
tickers={m1,m2,m3,m4};
for i=1:length(tickers)
    prices{i}=xlsread('Stocks.xlsx','29112013','30112017',tickers{:,i});
end
for i=1:length(tickers)
    p(:,i)=prices{1,i}.Close;
    plow(:,i)=prices{1,i}.Low;
end

[Benjamin Kraus]

I cannot edit your question, but your code needs to indented so that it is formatted as code. For the sake of others:

m1=input('Insert the first stock: ');
m2=input('Insert the second stock: ');
m3=input('Insert the third stock: ');
m4=input('Insert the fourth stock: ');
tickers={m1,m2,m3,m4};
for i=1:length(tickers)
  prices{i}=xlsread('Stocks.xlsx','29112013','30112017',tickers{:,i});
end
for i=1:length(tickers)
  p(:,i)=prices{1,i}.Close;
  plow(:,i)=prices{1,i}.Low;
end

Answer 1

[Benjamin Kraus]

The first issue is the command:

prices{i}=xlsread('Stocks.xlsx','29112013','30112017',tickers{:,i});

You are specifying the 'xlRange' using dates encoded as character vectors, which are not meaningful to Excel or MATLAB. You need to revisit the documentation for xlsread, as the command above does not match any syntax listed. xlsread is expecting you to "Specify xlRange using two opposing corners that define the region to read. For example, 'D2:H4' represents the 3-by-5 rectangular region between the two corners D2 and H4 on the worksheet."

The bigger issue is the overall strategy of using individual calls to xlsread to extract individual pieces of information from the Excel spreadsheet. Calling xlsread is relatively slow and expensive. In addition, before you can index into the spreadsheet based on dates, MATLAB would need to first read the entire spreadsheet, locate the lines of interest, and then throw away the rest.

As long as your data fits into memory, you are much better off reading the entire spreadsheet into MATLAB (using either xlsread or the newer more powerful readtable). Once you have the entire table in MATLAB, you can extract the pieces of the table as desired very quickly and easily.

You may also want to check out the Import Data Wizard (using the big "Import Data" button on the MATLAB tool strip. It will walk you through the process of importing your data, and if you want it can generate code for you to show you the proper commands to repeat the import process automatically next time.