MATLAB Answers

I want to read in an Excel table with heterogeneous data types

12 views (last 30 days)
I can't find a format example, IO need to read in a data format, some string fields and double precision variables. What does the format option look like? e.g. %d. This is my code so far:
fname='Curves 02.13.18.xls';
T=readtable(fname,'ReadRowNames',true,format,{%d});
Bid = T(:,7);
Ask = T(:,8);
Mid = T(:,9);
Settle = T(1,1);
  7 Comments
Guillaume
Guillaume on 20 Feb 2018
Not sure if help is still needed since an answer has been accepted but for me, using R2017b
T = readtable('Curves 02.13.18.xls')
is enough to get the table read properly.
In any case, since the excel file does not have row headers, using 'ReadRowNames', true| was a mistake. 'ReadVariableNames', true would have been more appropriate but that's the default so is not needed.

Sign in to comment.

Accepted Answer

Brandon Armstrong
Brandon Armstrong on 20 Feb 2018
One approach is to right-click on the data file in the Current Folder and select "Import Data." Then you can interactively select the data to import, set variable names, what to do with missing data, and what data-type to import each column. You can then use the Generate Script/Function button if you need to repeat this on spreadsheets saved the same way. I usually use this approach if I just need to import something once or if I don't care what the generated code looks like.
Another approach, which I use if I need to repeat the reading of the data, or keeping a record of how I read in the data is important is to use the detectImportOptions function. You can then modify the properties of this object to specify how the data should imported when using readtable. You can specify the number of header lines and data types and then pass the import options object to readtable.

More Answers (1)

Peter Perkins
Peter Perkins on 20 Feb 2018
Julian, Brandon's suggestions fo detectImportOptions and the Import Tool are good ones, but if all your spreadsheet has in it is text and numeric, it's likely that all you need to pass into readtable is the file name. Of course, financial data often has timestamps, but in recent versions of MATLAB, those are automatic as well. In earlier, you may need to read them as text and then convert.
Without an example of what's in the file, pretty hard to say.
  2 Comments
Peter Perkins
Peter Perkins on 21 Feb 2018
By "timestamps", I meant dates or dates+times. In any case, in recent versions of MATLAB, you don't need to do anything special to read that spreadsheet:
>> t = readtable('Curves 02.13.18.xls');
Converting some of those variables to categorical is a good idea (you could also do that with detectimportoptions before reading):
>> t.CURRENCY = categorical(t.CURRENCY);
>> t.DAY_COUNT = categorical(t.DAY_COUNT);
>> t.FREQUENCY = categorical(t.FREQUENCY);
>> t
t =
22×17 table
VALUATION_DATE SHIFTED_BY CURRENCY DAY_COUNT FREQUENCY DAILY_FIXING BID ASK MID ZC_BID ZC_ASK ZC_MID DF_BID DF_ASK DF_MID ID_DATE ID
______________ __________ ________ __________ _________ ____________ ______ ______ ______ ______ ______ ______ _______ _______ _______ ___________ _____
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.396 1.446 1.421 1.4342 1.4342 1.4342 0.99882 0.99882 0.99882 15-Mar-2018 '1M'
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.485 1.535 1.51 1.5234 1.5234 1.5234 0.99742 0.99742 0.99742 16-Apr-2018 '2M'
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.53 1.58 1.555 1.5687 1.5687 1.5687 0.9961 0.9961 0.9961 15-May-2018 '3M'
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.557 1.607 1.582 1.5957 1.5957 1.5957 0.99468 0.99468 0.99468 15-Jun-2018 '4M'
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.604 1.654 1.629 1.6422 1.6422 1.6422 0.99314 0.99314 0.99314 16-Jul-2018 '5M'
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.637 1.687 1.662 1.6745 1.6745 1.6745 0.99164 0.99164 0.99164 15-Aug-2018 '6M'
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.718 1.768 1.743 1.7527 1.7527 1.7527 0.98688 0.98688 0.98688 15-Nov-2018 '9M'
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.793 1.843 1.818 1.8239 1.8239 1.8239 0.98183 0.98183 0.98183 15-Feb-2019 '1Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.903 1.943 1.923 1.9328 1.9328 1.9328 0.9714 0.9714 0.9714 15-Aug-2019 '18M'
13-Feb-2018 0 USD Actual/360 Annual 1.42 1.994 2.044 2.019 2.0265 2.0265 2.0265 0.96001 0.96001 0.96001 18-Feb-2020 '2Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.1205 2.1605 2.1405 2.1496 2.1496 2.1496 0.93733 0.93733 0.93733 16-Feb-2021 '3Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.2032 2.2323 2.2178 2.2283 2.2283 2.2283 0.91456 0.91456 0.91456 15-Feb-2022 '4Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.2542 2.2942 2.2742 2.2861 2.2861 2.2861 0.89182 0.89182 0.89182 15-Feb-2023 '5Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.3447 2.3847 2.3647 2.3797 2.3797 2.3797 0.84617 0.84617 0.84617 18-Feb-2025 '7Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.4377 2.4678 2.4527 2.4721 2.4721 2.4721 0.78076 0.78076 0.78076 15-Feb-2028 '10Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.49 2.49 2.49 2.5116 2.5116 2.5116 0.73953 0.73953 0.73953 15-Feb-2030 '12Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.5055 2.5455 2.5255 2.5494 2.5494 2.5494 0.68193 0.68193 0.68193 15-Feb-2033 '15Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.526 2.566 2.546 2.5688 2.5688 2.5688 0.59791 0.59791 0.59791 16-Feb-2038 '20Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.5408 2.5408 2.5408 2.5577 2.5577 2.5577 0.52723 0.52723 0.52723 17-Feb-2043 '25Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.5055 2.5455 2.5255 2.5339 2.5339 2.5339 0.4672 0.4672 0.4672 18-Feb-2048 '30Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.4903 2.4903 2.4903 2.4791 2.4791 2.4791 0.37066 0.37066 0.37066 15-Feb-2058 '40Y'
13-Feb-2018 0 USD Actual/360 Annual 1.42 2.4548 2.4548 2.4548 2.4214 2.4214 2.4214 0.29771 0.29771 0.29771 15-Feb-2068 '50Y'

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!