Matlab: Saving data in excel then extracting data for calculations by using a date serial as the search parameter.

So what I am trying to do is to save data into an excel document sorted by the date with 5 inputs aligned in the columns next to it. I have been able to create the excel file but now I am struggling with how to extract the inputs by searching for the matching date as selected by the user.

If someone can point me in the right direction that would be great. I am pretty sure I need to use a 'if-statement' so the script reads the excel file until it finds a matching date, but I am not to sure how to search an excel document by the date.

I am using the serial date for my code (i.e. 737400 = 12-Dec-2018)

This is the code I currently have:

% ============= DATA ENTRY WINDOW ===============
                                                            
    function cbDataEntry(~, ~)
        figure('Position', [30, 30, 400, 400], ...
            'Name', 'Data Entry', ...
            'MenuBar', 'none', ...
            'NumberTitle', 'off', ...
            'Resize', 'off');
        uicontrol('Style', 'Text', ...                      % Date Text
            'String', 'Date:', ...
            'Horizontalalignment', 'left', ...
            'Position', [15 370 40 20]);
        dateEntry = uicontrol('Style', 'Edit', ...          % Date Display
            'Position', [45 372 100 20], ...
            'BackgroundColor', 'w');
        uicontrol('Style', 'Pushbutton', ...                % Date Select
            'String', 'Select Date', ...
            'Position', [160 372 100 20], ...
            'callback', @cbCalendar);
        uicontrol('Style', 'pushbutton', ...                % Save Button
            'String', 'Save Data', ...
            'Position', [210 20 80 25], ...
            'callback', @cbSave);
        uicontrol('Style', 'pushbutton', ...                % Close Button
            'String', 'Close', ...
            'Position', [300 20 80 25], ...
            'callback', @cbClose);
        uicontrol('Style', 'Text', ...                      % Input1 Text
            'String', 'Input1:', ...
            'Horizontalalignment', 'left', ...
            'Position', [15 300 40 20])
        Input1 = uicontrol('Style', 'Edit', ...             % Input1 Entry
            'callback', @cbInput1, ...
            'Position', [50 302 75 20]);
        uicontrol('Style', 'Text', ...                      % Input2 Text
            'String', 'Input2:', ...
            'Horizontalalignment', 'left', ...
            'Position', [15 270 40 20])
        Input2 = uicontrol('Style', 'Edit', ...             % Input2 Entry
            'callback', @cbInput2, ...
            'Position', [50 272 75 20]);
        uicontrol('Style', 'Text', ...                      % Input3 Text
            'String', 'Input3:', ...
            'Horizontalalignment', 'left', ...
            'Position', [15 240 40 20])
        Input3 = uicontrol('Style', 'Edit', ...             % Input3 Entry
            'callback', @cbInput3, ...
            'Position', [50 242 75 20]);
        uicontrol('Style', 'Text', ...                      % Input4 Text
            'String', 'Input4:', ...
            'Horizontalalignment', 'left', ...
            'Position', [15 210 40 20])
        Input4 = uicontrol('Style', 'Edit', ...             % Input4 Entry
            'callback', @cbInput4, ...
            'Position', [50 212 75 20]);
        uicontrol('Style', 'Text', ...                      % Input5 Text
            'String', 'Input5:', ...
            'Horizontalalignment', 'left', ...
            'Position', [15 180 40 20])
        Input5 = uicontrol('Style', 'Edit', ...             % Input5 Entry
            'callback', @cbInput5, ...
            'Position', [50 182 75 20]);
                                                            
        function cbCalendar(~, ~)                           % Calendar
            global dates
            uicalendar('SelectionType', 1, ...
               'DestinationUI', {dateEntry, 'String'});
            waitfor(dateEntry, 'String');
            d = get(dateEntry, 'String');
            dateformat = 'dd-mmm-yyyy';
            dates = datenum(d,dateformat);
        end
        
        function cbInput1(~, ~)                             % Input1
            global x
            x = str2double(Input1.String);
        end
        
        function cbInput2(~, ~)                             % Input2
            global y
            y = str2double(Input2.String);
        end
        
        function cbInput3(~, ~)                             % Input3
            global z
            z = str2double(Input3.String);
        end
        
        function cbInput4(~, ~)                             % Input4
            global g
            g = str2double(Input4.String);
        end
        
        function cbInput5(~, ~)                             % Input5
            global h
            h = str2double(Input5.String);
        end
        
        function cbSave(~, ~)                               % Save and create excel spreadsheet   <<<===== WHERE I CREATE THE SPREADSHEET
            global dates x y z g h
            filename = 'DataFile.xlsx';
            fileExist = exist(filename,'file'); 
            if fileExist==0
                title = {'Date  ', 'Input1', 'Input2', 'Input3', ...
                    'Input4', 'Input5'};
                xlswrite(filename,title);
            else
                [~,~,input] = xlsread(filename);
                new_data = {dates, x, y, z, g, h};
                output = cat(1,input,new_data);
                xlswrite(filename,output);
            end
        end
        
        function cbClose(~, ~)                              % Close
            close
        end
    end
% ============= DATA VIEWING WINDOW =============

    function cbDataLoad(~, ~)
        figure('Position', [30, 30, 400, 400], ...
               'Name', 'Data Viewer', ...
               'MenuBar', 'none', ...
               'NumberTitle', 'off', ...
               'Resize', 'off');
        uicontrol('Style', 'Text', ...                      % Date Text
                'String', 'Date:', ...
                'Horizontalalignment', 'left', ...
                'Position', [15 370 40 20]);
        dateSelect = uicontrol('Style', 'Edit', ...         % Date Display
                'Position', [45 372 100 20], ...
                'BackgroundColor', 'w');
        uicontrol('Style', 'Pushbutton', ...                % Date Select
                'String', 'Select Date', ...
                'Position', [160 372 100 20], ...
                'callback', @cbCalendar);
        uicontrol('Style', 'pushbutton', ...                % Load Button
            'String', 'Load Data', ...
            'Position', [210 20 80 25], ...
            'callback', @cbLoad);
        uicontrol('Style', 'pushbutton', ...                % Close
            'String', 'Close', ...
            'Position', [300 20 80 25], ...
            'callback', @cbClose);

        function cbCalendar(~, ~)                           % Calendar
            global dates2
            uicalendar('SelectionType', 1, ...
                'DestinationUI', {dateSelect, 'String'});
            waitfor(dateSelect, 'String');
            d = get(dateSelect, 'String');
            dateformat = 'dd-mmm-yyyy';
            dates2 = datenum(d,dateformat);
        end
        
        function cbLoad(~, ~)                               % Load Data from spreadsheet and make perform calculations  <<<==== WHERE I WANT TO GET THE DATA BACK
            global dates dates2
            filename = 'DataFile.xlsx';
            if dates == dates2
            end
        end
        
        function cbClose(~, ~)                              % Close
            close
        end
    end

[Kevin Chng]

Hi

is input1-5 the date? Then i think probably you are looking solution at here

function cbLoad(~, ~)                               % Load Data from spreadsheet and make perform calculations  <<<==== WHERE I WANT TO GET THE DATA BACK
            global dates dates2
            filename = 'DataFile.xlsx';
            if dates == dates2
            end
end

However, I'm bit confusing with what are dates and dates2? Try to avoid to use global in next time.

[Wyatt Guggisberg]

"dates" is a variable I am using to store the dates into the excel sheet.

        function cbCalendar(~, ~)                           % Calendar
            global dates
            uicalendar('SelectionType', 1, ...
               'DestinationUI', {dateEntry, 'String'});
            waitfor(dateEntry, 'String');
            d = get(dateEntry, 'String');
            dateformat = 'dd-mmm-yyyy';
            dates = datenum(d,dateformat);
        end

"dates2" is the variable I am using to compare to "dates" in the excel sheet.

[Kevin Chng]

if you want load data from excel, you should

tableA = readtable('DataFile.xlsx')

Here you may use indexing to get the i th of rows when the dates is match, for example

index = tableA.dates == dates2
newTable = tableA(index,:)

Will it work for you?

[Wyatt Guggisberg]

Ok that works beautifully! Thank you very much, you just saved me a boat load of time and frustration.

        function cbLoad(~, ~)
            global dates2
            DataTable = readtable('DataFile.xlsx');
            index = DataTable.Date == dates2;
            Values = DataTable(index,:);
            disp(Values)
            q = Values.Input1
            w = Values.Input2
            e = Values.Input3
            r = Values.Input4
            t = Values.Input5
        end

[Kevin Chng]

Do you mind accept my answer?It will give me reputation point.

Answer 1

[Kevin Chng]

if you want load data from excel, you should

tableA = readtable('DataFile.xlsx')

Here you may use indexing to get the i th of rows when the dates is match, for example

index = tableA.dates == dates2
newTable = tableA(index,:)