LoadingData

Introduction

Usually you will have data saved in some file, like Excel files, csv (comma seperated values) file or a text file. These are the most common formats in which you can download data from various databases.

Before we can look at the data upload procedure it is important to have a very quick look at the way in which MATLAB deals with dates.

Date formats

When dealing with time series data you will often want to keep date information. MATLAB has excellent date functionality and dealing with data is indeed one of its strengths. There are two principle formats in which MATLAB handles dates, the datenum and the datevec format

1. The datenum format: Here MATLAB records date information in terms of the number of days since 01 Jan 0000. This day is given the number 1. The next day (2 Jan 0000) is assigned the number 2 and so forth. The 22 Sept 2012 is 735134. If you have time information included into your dates, that will be reflected in fractions. For example, '22-Sept-2012 12:00:00' is represented by 735134.5, as it is half way through the day.

1. The datevec format: Here a date is transformed into a (1 x 6) vector where the first element represents the year, the second the month, the third the day, the fourth the hour, the fith the minutes and the sixth the seconds. If you only enter a day, the last three entries will take the value 0.

It is a great feature of MATLAB that it recognises a good number of different date formats and translates them easily into one of the two MATLAB formats. Importantly, if you open an EXCEL file (see below) and your spreadsheet contains dates formatted as EXCEL dates, MATLAB will automatically translate these dates into the datenum format if you are using a MATLAB version R2012a or higher.

Data Sources

By far the easiest formats to import data into MATLAB are Excel spreadsheets and csv files. When dealing with these type of files you can double click on the file you want to import in the "Current Directory" window. A window will then open and give you a preview of the data MATLAB will import. How exactly this wimdow looks like and what translation MATLAB does automatically now depens on the MATLAB version you are working with.

EXCEL files

MATLAB R2011 and lower

Generally MATLAB will generate the data in two matrices, one for the columns that MATLAB recognised as numerical data (often called data) and another that contains all non-numerical spreadsheet entries (often called textdata). You can then use these data matrices for any further operations. Date cells will end up in the textdata matrix and you will have to convert them manually to the MATLAB dates files (see below).

Instead of double clicking on a file in the "Current Directory" window you can also incorporate the data import into a piece of MATLAB script. This is done with the following command:

   [NUM,TXT,RAW] = xlsread('MSFT.xlsx') 

which will save the content of the EXCEL file (media:MSFT.xlsx) into three variables. The complete spreadsheet is saved in RAW, all numerical entries are in NUM and all text entries are in TXT. This command works if the data you want to import are on the first sheet in the EXCEL file. If you have several shhets in your EXCEL file and you want to import the data of a particular sheet you can achieve this by means of:

   [NUM,TXT,RAW] = xlsread('MSFT.xlsx','MSFT') 

where the data here are on the sheet called 'MSFT'. Also consult doc xlsread for more details on this import function.

MATLAB R2012 and higher

Importing EXCEL data works in the same way as for earlier versions if you use the command line. The difference occurs if you import data by double clicking on the file. Now MATLAB will automatically recognise if any of the input data are dates and if it finds any suggest that these are imported as datenum type data.

Often MATLAB will get the dates right, but there are situations where MATLAB does not get the date-info correctly. One reason for this can be that the MATLAB and Excel are using different reference days. As mentioned above, day 1 in MATLAB is 1 Jan 0000, whereas day one in Excel is 1 Jan 1900. It is prudent to check that MATLAB has done the right thing.

Here is some guidance on how to do that. Let's say you are importing a dataset where you know that the first observation is for 22 Sept 2012. Once the data are in MATLAB check what the value for the first date is. If it is still in text format, i.e. "22/9/2012", then you know that you are yet to translate it into a MATLAB date format. However, if you encounter the number 735134, then you know that MATLAB has already translated the date into its own format. Whether it has done so correctly you can check using

>> datevec(735134)
ans =
        2012          9          22           0           0           0

which returns the correct date (in the "YYYY MM DD hh mm ss" format). If however MATLAB has the number 41174 as an entry, then the translation did not take the different reference date into account. The number is the Excel date number (as 22 Sept 2012 is the 41174th day when starting with 1 Jan 1900).

How can you correct this? Fortunately the correction is pretty easy. Lets assume that your dates are in a vector called dateold, then you can re-base the date vector as follows:

 datenew = dateold + (735134-41174)   # rebasing date vector

When you do that, it is vital that you add a comment to the code to remember why you did that. Otherwise it will the reason for a lot of head-scratching if you open the code a few months later.

CSV files

To import csv files you can also double click on the file in the "Current Directory" window. In this case MATLAB will basically do exactly the same as if the csv file was a xlsx file (see above). You can also import data from a csv file through a command line.

   DATA = csvread('MSFT.csv') 

If you execute this line with this LINK TO THIS FILE!!!! 'MSFT.csv' file you will actually get an error message. This file contains the date information in the first column and the csvread function does not really know how to handle these. To avoid this, you can tell MATLAB to only import a certain section of the spreadsheet. For instance, if you want it to not import the first row and first column (which in this partivular case contains the non-numeric information) you can use

   DATA = csvread('MSFT.csv',1,1) 

where the first '1' indicates that MATLAB should ignore one row at the top of the sheet and the second '1' instructs MATLAB to ignore one column at the left of the sheet. Also consult doc csvread for more details on this import function. However, if you need the date information it is easier to first save the file as an Excel file and then import into MATLAB.

It is often easier to use the xlsread function with csv files. That usually works in a straightforward manner.

Dealing with Date vectors

As mentioned above there are two date formats that can be used in MATLAB. Depending on what you want to achieve one or the other may be easier to work with. The good thing is that you can translate back and forth between the two formats at will. Let's try that using the following date: 30 April 1777, the birthday of Carl Friedrich Gauss.

a = datenum('30.04.1777','dd.mm.yyyy')
a = 649157

Hence CF Gauss was born on the 649,157th day if you start counting on 1 Jan 0000. You can see from the above line that the second input 'dd.mm.yyyy' told MATLAB where to find the day, month and year information. If you are more familiar with the American way of formatting dates you could have achieved the same with:

a = datenum('04/30/1777','mm/dd/yyyy')
a = 649157

If you preferred to use the datevec format, you could have done the following (different date formats are used in exactly the same way).

b = datevec('04/30/1777','mm/dd/yyyy')
b = 1777           4          30           0           0           0

Just to make sure that both formats understand each other you could type

datenum(b)
ans = 649157

So far we always used one particular date as input, but both these functions can also deal with vectors as input, which is exactly what MATLAB does when it imports a column with dates from an EXCEL sheet. There is a note of caution when working with dates. As mentioned above there are different conventions as far as the ordering between days and months are concerned. You need to make sure that you understand which convention was used in your data source file and adjust the code accordingly. As demonstrated above, MATLAB can easily deal with these as long as you instruct MATLAB accordingly. MATLAB's default date format is mm-dd-yyyy.

Useful date operations

Date information can be extremely useful. For instance you could ask how many days ago CF Gauss was born (assuming that today is 29 September 2012):

now = date;    % defines now as the current day
datenum(now) - a
ans = 85979

Here we used the MATLAB function date which will retrieve the current date. Now you could ask what weekday the 30 April 1777 was.

weekday(a)    % use a datenum date as input
ans = 4

The answer 4 indicates that this particular genius was born on a Wednesday (1 = Sunday, 2 = Monday, etc.).

Example Video

This video clip demonstrates the import of data from an Excel file and some of the basic date formatting. ExampleClip