Cannot import Excel xls file

18 views (last 30 days)
Sky Trader
Sky Trader on 14 Nov 2019
Answered: Image Analyst on 9 Dec 2020
Hi, I don't understand why I can no longer use xls files? Never had a problem before but if I use import data my xls files are greyed out in my file directory so Matlab can't import it? I have read a few threads here about this but really don't understand how to implent comoplicated solutions and script fixes. I just want the software to open like it used to. Already spent an hour and nada... Beyond fruistrating when everything used to work fine when I last used Matlab 6 months ago. Any help much appreciated. Thanks
  2 Comments
Mil Shastri
Mil Shastri on 14 Nov 2019
What function are you using to read the excel file, have you tried readtable?. Also, feel free to upload a sample excel file.

Sign in to comment.

Answers (7)

Sky Trader
Sky Trader on 14 Nov 2019
Hi Walter and Mil, I'm using a Macbook Pro (10.14.6) with an old Excel (2011). I'm not sure what you mean about function? Sorry I litterally just use Matlab to import Excel data files and plot graphs. So I normally would simply go to "import data" and then use the apps. It was dead simple... until tonight.
I don't know what "readtable" is or where/how to execute it?
The file is the Daily Returns for the Dow Jones (1985-2019) - attached. Even previous files I've worked on before are now all greyed out when I try to select them for import.
The only thing that ws odd tonight was I saw a Matlab message I've never seen before that asked me to allow permission to read my photo files, which I denied without even thinking, and now that maybe this might be linked?
Cheers.
  2 Comments
Mil Shastri
Mil Shastri on 15 Nov 2019
What's different than what appears for me?
importdata.PNG

Sign in to comment.


Sky Trader
Sky Trader on 15 Nov 2019
Thanks Mil. When I add your fileattrib line: fileattrib('DJI DAILY 1985-2019 Std Dev copy 2.xls') it says: (pls see image).
(and that's assuming I'm entering this in the correct command window place as I've never had to use the command window before).
What's happening now after starting up my Mac this morning and relaunching Matlab is that these files are now not greyed out and I can import them. Phew! What I don't understand is why yesterday when I hit Home/Import Data my Mac file directory is greyed out - even old xls files that I know I've imported in the past were greyed out and unselectable?
I was attempting to make a distrubution of Dow returns probability density function graph. Could you please tell me how to do that because when I select and Distribution Fitter I don't see how to pull the newly imported data in to that particular App, because when I go to File Import Data there's nothing in the drop down menu to select? Pls see image.
Because it's been a while since I used Matlabs I also now can't figure out how I did Curve Fitting. I'm sure I used to select 3 (xls) columns from the workspace and then just launch the Curve Fitter App and it produced a coloured 3-d graph. I can't seem to load those 3 columns into the Curve Fitter -- it just says "untitled fit 1" (although I can see that loading old sessions works fine)?
Lastly, I was wondering why are there no Plots available (pls see image)?
Thanks very much for your help.
  1 Comment
Mil Shastri
Mil Shastri on 15 Nov 2019
The distribution filter app requires data to be vectors. You can do this by creating separate vectors from selected columns in a table as shown below:
importdata.PNG
As always, some good detailed information about using the app can be found in the MATLAB documenation here https://www.mathworks.com/help/stats/model-data-using-the-distribution-fitting-tool.html
On a side note, you can also have MATLAB generate code automatically for all the GUI based customizations that you may have performed using DistributionFilterApp > File > GenerateCode . This can be handy if at all you would like to automate your scripts in future.

Sign in to comment.


Sky Trader
Sky Trader on 18 Nov 2019
Thanks for the further explanation. I've never used that New Workspace variable command before to bring in data with the Curve Fitting app.
I tried selecting columns from within the Dow data and can neither get anything to show in the Curve app (using 3 columns) or Distribution Fitter app (using just the Returns column).
I imported the Dow Jones single returns column (left side of my screenshot and then double clicked on the Returns in Workspace) and used Distribution Fitter but still no file / data is brought into this app -- and I used the New Workspace variable command?
Why do I have greyed out Plots?
I also now use the Excel extension .xlt -- how do I get Matlab to recognise it to import them?
Cheers.

Sky Trader
Sky Trader on 20 Nov 2019
Hi, is there anyone on this forum that can simply explain why I cannot import excel data anymore? A task I used take seconds doing is now completely impossible. Thanks.
  3 Comments
Mil Shastri
Mil Shastri on 21 Nov 2019
That was probably it :)

Sign in to comment.


Sky Trader
Sky Trader on 21 Nov 2019
Please. Just SIMPLE instructions.
Go to x
Click on Y

Sky Trader
Sky Trader on 22 Nov 2019
Thanks Walter.
Now it opens in "Plots" using the Curve Fitting tool (which I never used) but won't open in "Apps" which is how I've always done analysis, (using the Curve Fitting app)? Pls see image.

Image Analyst
Image Analyst on 9 Dec 2020
For what it's worth, I'm attaching data and a demo to compute the volatility of the US Stock Market Dow Jones Index.
It seems like the TV analysts are always saying that we're in a period of historically high volatility. But is that really true (no) or are they just trying to make the new exciting (yes)? Well I downloaded the data from Yahoo finance and did my own analysis. The long term average day-to-day percent change of the index is 0.75%.
It's a very short and simple program that I'm sharing below (and attached with the data file).
% Program to plot the Dow Jones Industrial Average from 1985 to August 2019, and compute the standard deviation.
% Data available for download at https://finance.yahoo.com/quote/%5EDJI/history?p=%5EDJI
% Just set the Time Period, then find and click the download link.
clc; % Clear the command window.
close all; % Close all figures (except those of imtool.)
imtool close all; % Close all imtool figures if you have the Image Processing Toolbox.
clear; % Erase all existing variables. Or clearvars if you want.
workspace; % Make sure the workspace panel is showing.
format long g;
format compact;
fontSize = 14;
filename = 'Dow Jones Industrial Index.csv'
data = readtable(filename)
% Date,Open,High,Low,Close,Adj Close,Volume
dates = data.Date;
closing = data.Close;
subplot(3, 1, 1);
plot(dates, closing, 'b-');
xticks(datetime(1985,1,1):calendarDuration(2,0,0):datetime(2021,1,1))
drawnow;
grid on;
title('Dow Jones Industrial Average', 'FontSize', fontSize);
% Get the average change from one trading day to the next.
diffs = 100 * abs(closing(2:end) - closing(1:end-1)) ./ closing(1:end-1);
subplot(3, 1, 2);
averageDailyChange = mean(diffs)
numWeeks = 4;
diffs = sgolayfilt(diffs, 2, 5*numWeeks+1);
plot(dates(2:end), diffs, 'b-');
grid on;
xticks(datetime(1985,1,1):calendarDuration(2,0,0):datetime(2021,1,1))
hold on;
line(xlim, [averageDailyChange, averageDailyChange], 'Color', 'r', 'LineWidth', 2);
caption = sprintf('Day-to-Day Change Percentage. Average Daily Change (from prior day) = %.2f%%', averageDailyChange);
title(caption, 'FontSize', fontSize);
drawnow;
% Get the stddev over a 5 trading day window.
sd = stdfilt(closing, ones(5, 1));
% Get it relative to the magnitude.
sd = sd ./ closing * 100;
averageVariation = mean(sd)
numWeeks = 40;
sd = sgolayfilt(sd, 2, 5*numWeeks+1);
subplot(3, 1, 3);
plot(dates, sd, 'b-');
grid on;
xticks(datetime(1985,1,1):calendarDuration(2,0,0):datetime(2021,1,1))
hold on;
line(xlim, [averageVariation, averageVariation], 'Color', 'r', 'LineWidth', 2);
caption = sprintf('Weekly Standard Deviation, Averaged Over %d Weeks (%d trading days). Mean SD = %.2f', ...
numWeeks, 5*numWeeks+1, averageVariation);
title(caption, 'FontSize', fontSize);
MaximizeFigureWindow;
Historically the four highest volatility periods were all high sell off periods:
  1. the Oct 1987 crash
  2. the 2002 dot com bust
  3. the late 2008 financial crisis, and
  4. the Feb. 2020 COVID crash.
From the plots you can see that from 2003 to the present, the volatility was mostly below average (except for the two crashes) while the period early 1997 - early 2003 it was mostly above the long term average.

Community Treasure Hunt

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

Start Hunting!