Datetime errors when I import data

13 Ansichten (letzte 30 Tage)
William Milton
William Milton am 4 Aug. 2022
Kommentiert: Cris LaPierre am 4 Aug. 2022
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 Kommentare
dpb
dpb am 4 Aug. 2022
Without the format of the data you're trying to read, not much we can do...attach a sample input file or at least give an example string to try to convert.
William Milton
William Milton am 4 Aug. 2022
This is the file I'm trying to import, it's an Excell file (.xlss)

Melden Sie sich an, um zu kommentieren.

Akzeptierte Antwort

Cris LaPierre
Cris LaPierre am 4 Aug. 2022
Bearbeitet: Cris LaPierre am 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 Kommentare
William Milton
William Milton am 4 Aug. 2022
At the end I solved importing data as vector column, then I wrote this 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 18:51:58
%% 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
tbl = readtable("C:\Users\William\Desktop\TestMatlab.xlsx", opts, "UseExcel", false);
%% Convert to output type
Scadenza = tbl.Scadenza;
PrezzoMercato = tbl.PrezzoMercato;
Cedola = tbl.Cedola;
%% Clear temporary variables
clear opts tbl
Settle = repmat(datenum('12-Set-2001'),[20 1]);
Maturity = datenum(Scadenza(:,1));
CleanPrice = PrezzoMercato(:,1);
CouponRate = Cedola(:,1);
Instruments = [Settle Maturity CleanPrice CouponRate];
PlottingPoints = datenum(Scadenza(1,1)):180:datenum(Scadenza(20,1));
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')
It seems to work perfectly
Cris LaPierre
Cris LaPierre am 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')

Melden Sie sich an, um zu kommentieren.

Weitere Antworten (1)

Steven Lord
Steven Lord am 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 Kommentar
William Milton
William Milton am 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

Melden Sie sich an, um zu kommentieren.

Kategorien

Mehr zu Data Import from MATLAB finden Sie in Help Center und File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by