Datetime errors when I import data

6 views (last 30 days)
Hi, I'm a beginner in Matlab. I found out Matlab allows me to apply the Nelson-Siegel model easily for my thesis. Hence, I replicated this code https://it.mathworks.com/help/fininst/fitnelsonsiegelirfunctioncurve.html with my own data. As far as here it's all right. However it's time wasting to insert bond data manually so I tried to use the same code, but importing a dataset. This is the code:
%% Import data from spreadsheet
% Script for importing data from the following spreadsheet:
%
% Workbook: C:\Users\William\Desktop\TestMatlab.xlsx
% Worksheet: Foglio1
%
% Auto-generated by MATLAB on 04-Aug-2022 10:35:06
%% Set up the Import Options and import the data
opts = spreadsheetImportOptions("NumVariables", 3);
% Specify sheet and range
opts.Sheet = "Foglio1";
opts.DataRange = "A2:C21";
% Specify column names and types
opts.VariableNames = ["Scadenza", "PrezzoMercato", "Cedola"];
opts.VariableTypes = ["datetime", "double", "double"];
% Specify variable properties
opts = setvaropts(opts, "Scadenza", "InputFormat", "");
% Import the data
TestMatlab = readtable("C:\Users\William\Desktop\TestMatlab.xlsx", opts, "UseExcel", false);
%% Clear temporary variables
clear opts
Settle = repmat(datetime('12-Sep-2001'),[20 1]);
Maturity = datetime('Scadenza');
CleanPrice = ('PrezzoMercato');
CouponRate = ('Cedola');
Instruments = [Settle Maturity CleanPrice CouponRate];
PlottingPoints = datetime('15-Feb-2002'):180:datetime('01-Aug-2011');
Yield = bndyield(CleanPrice,CouponRate,Settle,Maturity);
NSModel = IRFunctionCurve.fitNelsonSiegel('Zero',datenum('12-Set-2001'),Instruments);
NSModel.Parameters
plot(PlottingPoints, getParYields(NSModel, PlottingPoints),'r','Color',[0 0 0])
hold on
scatter(Maturity,Yield,'black')
datetick('x')
So I get an datetime error that says:
"Error using datetime
Could not recognize the date/time format of 'Scadenza'. You can specify a format using the
'InputFormat' parameter. If the date/time text contains day, month, or time zone names in a language
foreign to the 'en_US' locale, those might not be recognized. You can specify a different locale using
the 'Locale' parameter".
I tried so follow the procedure but I get other errors.
Any advice?
  2 Comments
William Milton
William Milton on 4 Aug 2022
This is the file I'm trying to import, it's an Excell file (.xlss)

Sign in to comment.

Accepted Answer

Cris LaPierre
Cris LaPierre on 4 Aug 2022
Edited: Cris LaPierre on 4 Aug 2022
You are passing a character array into the datetime function, not a variable
Maturity = datetime('Scadenza');
Error using datetime
Could not recognize the date/time format of 'Scadenza'. You can specify a format using the 'InputFormat' parameter. If the date/time text contains day, month, or time zone names in a language
foreign to the 'en_US' locale, those might not be recognized. You can specify a different locale using the 'Locale' parameter.
This line of code is unnecessary, as you've already imported this table variable as datetime. It looks like instead this page on accessing data in tables is what you need.
So your code should be
Maturity = TestMatlab.Scadenza;
  2 Comments
Cris LaPierre
Cris LaPierre on 4 Aug 2022
Here's how I would do it.
fileNm = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/1088350/TestMatlab.xlsx';
tbl = readtable(fileNm)
tbl = 20×3 table
Scadenza PrezzoMercato Cedola ___________ _____________ ______ 15-Feb-2002 99.618 0.03 15-Apr-2002 99.518 0.03 15-Jun-2002 99.367 0.03 01-Sep-2002 99.955 0.0375 15-Jan-2003 100.97 0.045 15-Apr-2003 101.19 0.0475 15-Jun-2003 101.99 0.05 15-Oct-2003 102.78 0.0525 01-Feb-2004 98.094 0.0325 15-Feb-2004 102.56 0.05 15-Mar-2004 101.35 0.045 15-Apr-2004 97.867 0.0325 01-Jul-2004 101.37 0.045 15-Jul-2004 100 0.04 01-Jul-2005 101.91 0.0475 15-Dec-2005 103.62 0.0525
Maturity = datenum(tbl.Scadenza);
CleanPrice = tbl.PrezzoMercato;
CouponRate = tbl.Cedola;
Settle = repmat(datenum('12-Sep-2001'),[20 1]);
Instruments = [Settle Maturity CleanPrice CouponRate];
PlottingPoints = Maturity(1):180:Maturity(20);
Yield = bndyield(CleanPrice,CouponRate,Settle,Maturity);
NSModel = IRFunctionCurve.fitNelsonSiegel('Zero',Settle(1),Instruments);
NSModel.Parameters
ans = 1×4
6.5256 -2.5896 -3.9028 2.0291
plot(PlottingPoints, getParYields(NSModel, PlottingPoints),'r','Color',[0 0 0])
hold on
scatter(Maturity,Yield,'black')
hold off
datetick('x')

Sign in to comment.

More Answers (1)

Steven Lord
Steven Lord on 4 Aug 2022
When you selected options in the Import Tool, you told it that you wanted it to import your Scadenza data as a datetime array. But you didn't tell the tool the format in which your data had been stored, based on this line in the generated code.
opts = setvaropts(opts, "Scadenza", "InputFormat", "");
If you go back to the Import Tool and specify the format in which that data is stored in your file, that may avoid the error you received. See the "Specify Decimal Separator and Date Format" example on this documentation page for an example of how to specify that format. You can choose one of a set of predefined options or you can specify your own using the identifiers in the tables in the description of the Format property for datetime objects. It's the first entry in the Properties section on that documentation page.
This assumes that the time and date data stored in your file is in a consistent format through the whole file. If you switch data formats halfway through the file, that's more challenging.
  1 Comment
William Milton
William Milton on 4 Aug 2022
opts = setvaropts(opts, "Scadenza", "InputFormat", "");
I tried to change it before, but the output was the same.
At the end the easier solution was to import data as column vectors

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by