Can Matlab automatically detect a new value in Excel?

I am using a program called Stella, which writes its results to an Excel spreadsheet. I want that whenever Stella writes its new results, Matlab detects that the result is new and processes it automatically.

Can I do that? If yes, how?

Answer 1

[Rik]

Do you mean Matlab should watch the file to detect when the file edit date&time changes?

I am not aware of any listeners that can do that, but you could have a loop running to track this every few seconds. You can either use the OS tools to determine the date&time, but you could also use some hash function. I have included my draft below. This is still work in progress and given that it relies on ComputeNonCryptHash (and ETA_disp) this is not a secure or fool-proof method.

function H=HashFile(filename,varargin)
%Compute the hash of a file.
%
% The file is read as uint16, after which the optional parameters are forwarded to the
% ComputeNonCryptHash function.
%
% On a reasonably powerfull machine this will process a file at about 3MB/s (7 on a fast machine).
% For ML6.5 you should expect 1/8 the performance of a modern release.
%
%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%
%|  Author:  H.J. Wisselink                                                |%
%|  Licence: CC by-nc-sa 4.0 ( creativecommons.org/licenses/by-nc-sa/4.0 ) |%
%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%/%


% Because fread(fid,inf,'*uint16') might cause memory errors on large file, this function uses a
% looped system, where the file is read in chunks. The hash for each chunk is computed and stored
% in an array. When this array is full, the hash of the entire block is computed. This resultant
% hash is stored as the last line of a new block. This idea is similar to salting of hashes or to
% how a blockchain can work.
bytes_per_chunk = 1024*1024; % Number of bytes used for each line in the block
chuncks_per_block = 25;      % Number of lines in the block (excluding the salt line)

fid = fopen(filename,'r');
if fid<0 || ftell(fid)<0
    error('HJW:HashFile:FileReadFailed',...
        'The file could not be read. Check if it exists and if it allows reading.')
end

% Compute the null hash and parse input options.
[N,HashOpts] = ComputeNonCryptHash(uint8([]),varargin{:});

% Set up variables for ETA_disp/waitbar.
sz = dir(filename);sz=sz.bytes;
N_blocks = ceil(sz/(bytes_per_chunk*chuncks_per_block));
N_block = 0;then = now; %#ok<TNOW1>

uint16_per_chunk = round(bytes_per_chunk/2);
counter = 0;X = repmat(N,chuncks_per_block+1,1); % Use null hash as initial salt.
while ~feof(fid)
    % Read the next chunk of the file as uint16.
    x = fread(fid,uint16_per_chunk,'*uint16');
    
    % If the ledger is full, compute the hash over the entire block and use it as salt for the
    % remainder of the file.
    if counter==chuncks_per_block
        salt = ComputeNonCryptHash(X,HashOpts);
        X = [repmat(N,chuncks_per_block,1);salt];
        counter = 0;
        N_block = N_block+1;ETA_disp(N_blocks,N_block,then)
    end
    counter = counter+1;
    X(counter,:) = ComputeNonCryptHash(x,HashOpts);
end
fclose(fid);

% Only post the 100% update if there was a previous call to ETA_disp.
if N_block~=0,ETA_disp(1,1,then),end

% Compute final hash.
H = ComputeNonCryptHash(X,HashOpts);
end

[Ebru Angun]

For now, I have a few questions about your comments above:

1- Do I need a specific Matlab Toolbox for this Hashfile function?

2- Is it possible that ComputeNonCryptHash function is not included?

Thanks in advance.

[Rik]

You don't need any toolbox to run this function. Any Matlab release from the last 24 years should work (from version 6.5 onward).

You will need to download the ComputeNonCryptHash and ETA_disp functions separately. You can do so from the links in my answer.

[Ebru Angun]

The result is that it did not work. The reason, I think, is that when new data are written in Excel, the file is not saved (as is the case when these things are done automatically). So, MATLAB cannot realize that new data are written. We solved the problem by solving the Volterra-Lotka system of differential equations (predator-prey model) in MATLAB. Thanks for your effort.

[Image Analyst]

I'm not sure how "solving the Volterra-Lotka system of differential equations (predator-prey model)" would let you know that Excel has had new data poked into it. Excel knows, and I'm sure it sets some flag/property, but if the altered workbook is not saved to disk I think you'd have to use ActiveX to recognize that some process poked data into Excel.

[Ebru Angun]

If we solve the Volterra-Lotka system in MATLAB, then we don't use EXCEL. EXCEL was meant to be the intermediary tool, reachable from both STELLA and MATLAB. But now since we solve the model by MATLAB, we don't use STELLA and EXCEL. Thank you very much for all your answers. I have never used ActiveX.

Answer 2

[Image Analyst]

If you're using Windows and ActiveX, you can check out the properties and methods here:

https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.valuechange?view=excel-pia

Answer 3

[Image Analyst]

How about if you use dir to get the file date/time stamp and then if it's later than the last time you processed it then Stella must have changed it and your program can do it's thing.

for k = 1 : whenever
    d = dir('data.xlsx');
    thisDate = d.datenum;
    if thisDate ~= lastDate
        % Then Stella modified it.
        % Process it somehow.
        % Then set the last date to this date 
        % so we can see when it changes again.
        lastDate = thisDate;
    end
end % of loop where you check the workbook file.

[Ebru Angun]

It did not work; see my answer above. I also tried d.bytes.