The simplest and most robust method to import data into MATLAB is to use a correctly formatted Excel file
and the import wizard. The key to the import is to make certain the Excel file has a very particular structure with one variable per column amd its name for the column in the first position.
Note: remember to transform the date column into numerical format “Select data column >> right-click ->> cells format >> Number”.
Import the data from your xlsfile, in this case NDXdata.xls which rappresents the daily price of NASDAQ 100 index from 10-Oct-1985 to 16-Sep-2011.
File >> Import data >> Select your file >> Select your data >> Next >> Finish
You can import data using xlsread function directly from the command window, remember that yourfile.xls must be in the MATLAB current folder.
Import the data from the file AAPLdata.xls which rappresents the daily price of APPLE Inc. index from 07-Sep-1984 to 16-Sep-2011.
>> data = xlsread('AAPLdata.xls');
This command will read the data in sheet1 of xls and assign it to data in MATLAB, it
can handle a number of other situations (see help for more information).
Data can be exported to an Excel file using xlswrite. Extended information about an excel file, such as sheet names and can be read using the command xlsflinfo.
Note: MATLAB and Excel do not agree about dates. MATLAB tracks dates as days past January 1, 0000
(inclusive) while Excel tracks dates as day past January 1, 1900. Thus, to convert imported Excel dates into
MATLAB dates, datenum(’30DEC1899′) must be added to the column of data representing the dates.
>> data = xlsread('AAPLdata.xls','Cleaned'); >> dates = data(:,1); >> datestr(dates(1)) ans = 07-Sep-0084 >> dates = dates + datenum('30DEC1899'); >> datestr(dates(1)) ans = 07-Sep-1984
There is a short way but you need the Financial Toolbox,
>> data = xlsread('AAPLdata.xls','Cleaned','A2:G6819'); >> dates = data(:,1); >> datestr(dates(1)) ans = 07-Sep-0084 >> dates = x2mdate(dates); >> datestr(dates(1)) ans = 07-Sep-1984
Note: with xlsread we can also import a selection of the xls data.
>> data = xlsread('AAPLdata.xls','Cleaned','A3819:G6819'); >> dates = data(:,1); >> dates = dates + datenum('30DEC1899'); >> datestr(dates(1)) ans = 14-Oct-1999
And finally my way,
>> [data,text] = xlsread('AAPLdata.xls','uncleaned'); >> dates = datenum(text(2:end,1),'dd/mm/yyyy'); >> data = [dates,data]; >> datestr(data(1,1)) ans = 07-Sep-1984
For CSV data there is csvread command, Note that CSV files must not contain anything but numeric values.
If the file contains strings, such as variable names, the import will fail and this is not very nice especially if we download data from yahoo.
To over come this limitation, use the Import Wizard as for xls files. It is essentially identical.
Procedure to import data from Yahoo
- Open your browser and go to http://finance.yahoo.com,
- Click on the Investing tab,
- Enter the financial securities name or code in the text box and click on “get quotes” (eg. ^DJI – the DJ Industrial average),
- Click on Historical prices on the left column, select the range period and frequency and click on “get prices”,
- Go to the bottom of the page, click on “download the spreadsheet” and save the csv file.
Now you have two ways to import data that we have already seen,
- Transform the CSV file to an XLS file and then import it into MATLAB,
- Use the Import Wizard and import the CSV file.
Remember that you have to deal with the dates.
IMPORTANT: Yahoo provides data from the newest to oldest and not vice-versa so we have to reverse the order. We can do it from excel with sort function or directly from MATLAB using “end:-1:1” as index.
>> data = [datenum(textdata(2:end,1),'yyyy-mm-dd'), data]; >> data = data(end:-1:1,:);
Reading text files it they contains only numvers is straight forward,
>> data = textread('nomefile.txt');
- MATLAB Data Files
The native format for MATLAB data are known as MATLAB data files, or .mat files.
We can easily save it and any changes in the native MATLAB data format.
>> save DJIdata
This will produce a file DJIdata.mat containing all variables in memory, the filename can contain any valid character for MATLAB.
To save a subset of those variables in memory by entering
>> save filename var1 var2 var3
which produces a file filename.mat containing var1, var2 and var3.
Data is simply loading by
>> load filename.mat
One easy method to get data out of MATLAB is to call save with the arguments -double -ascii.
This will produce a tab delimited file of the variables listed. It is generally a good practice to only export one variable at a time using this method.
>> save filename var1 -ascii -double
would save var1 in a tab delimited text file. Alternative methods to export data include xlswrite, csvwrite and dlmwrite.
Some useful commands to clean your MATLAB environment:
- clear, removes ALL VARIABLES from the workspace, releasing them from system memory (BE CAREFUL!),
- clear name1 name2 name3 …, remove the listed variables name1 name2 name3,
- clc, clears all input and output from the Command Window display, giving you a “clean screen.”
- Download from yahoo finance: GOOGLE, IBM, Amazon.com, Ryanair and Nasdaq Composite. Range period: 1-Jan-2005 – last fryday,
- Import data to MATLAB in the way you prefer, remember to transform dates,
- Create the matrix “Price” composed by Dates and the adj prices of Amazon, Ibm, Google, Ryanair and Nasdaq Composite,
- Save Price matrix in MATLAB,
- Export Price matrix in the xls format in a sheet named “imef”.