How to calculate the weighted average

I have 3 companies. Each one of them has a ME, a Price and a Return (3 rows) with 6 elements each (6 columns). I want to find the Weighted Average of the 3 companies for each year (column). Multiple each element of the 1st Row with each element of the 3rd Row and then divide by the sum of weights (in each column). Then I need to repeat the same task for 10 different sheets.

Thank you

[Benjamin Thompson]

If you are wanting to do this in MATLAB rather than a spreadsheet application like Excel, then attach some of the sample input data.

[ALEXANDRA]

Hello,

Thank you for answering.

Attached you can find my file along with the calculations.

I know how to do the calculations in Excel. This is not a problem. However, in this example I have very few data. For my project, I have hundreds of data and I cannot do all these calculations one by one and I am not familiar with Macros in Excel.

I am a little bit familiar with Matlab thus why I am trying to do it in Matlab.

Also, for my project, I do not have only one Sheet but my file includes several sheets (in my example I include only 2) and at the end I need to combine all my results in one Row or Column

Answer 1

[Image Analyst]

This seems to work fine. It reads in all the sheets of your sample workbook (except the "Results " sheet), and computes the weighted means. You can then do whatever you want with that vector, like write it to the Results worksheet or whatever.

% Demo by Image Analyst
% Initialization Steps.
clc;    % Clear the command window.
close all;  % Close all figures (except those of imtool.)
clear;  % Erase all existing variables. Or clearvars if you want.
workspace;  % Make sure the workspace panel is showing.
format long g;
format compact;
fontSize = 18;
markerSize = 40;
ds = spreadsheetDatastore("wa.xlsx")
for k = 1 : numel(ds.Files)
    thisFileName = ds.Files{k};
    fprintf('Processing file :"%s"\n', thisFileName)
    worksheetNames = sheetnames(thisFileName);
    for s = 1 : numel(worksheetNames)
        thisSheetName = worksheetNames{s};
        % Skip the "Results worksheet
        if contains(thisSheetName, 'Results','IgnoreCase',true)
            continue;
        end
        thisData = readmatrix(ds.Files{k}, 'Sheet', thisSheetName);
        % Crop off first column and first row.
        thisData = thisData(2:end, 2:end);
        [rows, columns] = size(thisData);
        % Find last row of valid numbers before the nans start.
        lastRow = find(isnan(thisData(:, 2)), 1, 'first')-1;
        % Extract MV
        mvWeights = thisData(1 : 3 : lastRow, :);
        % Extract returns
        AReturn = thisData(3 : 3 : lastRow, :);
        % Sum the weights in each column
        sumOfWeights = sum(mvWeights, 1); % Sum each column, going down rows.
        % Compute weighted sums for this one worksheet in this one workbook:
        fprintf('  Weighted Sums for worksheet :"%s"\n', thisSheetName)
        weightedAverage = sum(mvWeights .* AReturn, 1) ./ sumOfWeights
    end
end
fprintf('Done!\n')

[ALEXANDRA]

Thank you.

I 'll try it.

[ALEXANDRA]

Thank you again.

Unfortunatelly, it does not work with the original file without the calculations. In my previous attachment, I included all the calculations that I did in Excel for explaining what I need to do.

Thank you.

[Image Analyst]

Yes, that's because this file is different. You just have to check if lastRow is empty, make it the total number of rows in the matrix.

% Demo by Image Analyst
% Initialization Steps.
clc;    % Clear the command window.
close all;  % Close all figures (except those of imtool.)
clear;  % Erase all existing variables. Or clearvars if you want.
workspace;  % Make sure the workspace panel is showing.
format long g;
format compact;
fontSize = 18;
markerSize = 40;
ds = spreadsheetDatastore("wa-*.xlsx")
for k = 1 : numel(ds.Files)
    thisFileName = ds.Files{k};
    fprintf('Processing file :"%s"\n', thisFileName)
    worksheetNames = sheetnames(thisFileName);
    for s = 1 : numel(worksheetNames)
        thisSheetName = worksheetNames{s};
        % Skip the "Results worksheet
        if contains(thisSheetName, 'Results','IgnoreCase',true)
            continue;
        end
        thisData = readmatrix(ds.Files{k}, 'Sheet', thisSheetName);
        % Crop off first column and first row.
        thisData = thisData(2:end, 2:end);
        [rows, columns] = size(thisData);
        % Find last row of valid numbers before the nans start.
        lastRow = find(isnan(thisData(:, 2)), 1, 'first')-1;
        if isempty(lastRow)
            lastRow = rows;
        end
        % Extract MV
        mvWeights = thisData(1 : 3 : lastRow, :);
        % Extract returns
        AReturn = thisData(3 : 3 : lastRow, :);
        % Sum the weights in each column
        sumOfWeights = sum(mvWeights, 1); % Sum each column, going down rows.
        % Compute weighted sums for this one worksheet in this one workbook:
        fprintf('  Weighted Sums for worksheet :"%s"\n', thisSheetName)
        weightedAverage = sum(mvWeights .* AReturn, 1) ./ sumOfWeights
    end
end
fprintf('Done!\n')

[ALEXANDRA]

Thank you very much. It is a great help for me.

I was also able to make some changes and have this code work for other spreadsheets that I need.

Now, I want to export the results into Excel

I know how to export the variable ‘weightedAverage’ (I can also see it on the Workspace) using the command ‘xlswrite’ but when I do it my Excel sheet has only the weighted averages of the last year.

I was wondering if there is a way for exporting all the weighted averages for both of the years 2019 and 2020 into 1 sheet of an Excel file.

I can see that on the Command window there are all the results.

Thanks a lot for your help.

[Image Analyst]

@ALEXANDRA, try this. Adapt as needed:

% Demo by Image Analyst
% Initialization Steps.
clc;    % Clear the command window.
close all;  % Close all figures (except those of imtool.)
clear;  % Erase all existing variables. Or clearvars if you want.
workspace;  % Make sure the workspace panel is showing.
format long g;
format compact;
fontSize = 18;
markerSize = 40;
rowHeader = {'Weighted Averages'};
ds = spreadsheetDatastore("wa-*.xlsx")
for k = 1 : numel(ds.Files)
	thisFileName = ds.Files{k};
	fprintf('Processing file :"%s"\n', thisFileName)
	worksheetNames = sheetnames(thisFileName);
	numWorksheets = 0;
	for s = 1 : numel(worksheetNames)
		thisSheetName = worksheetNames{s};
		% Skip the "Results worksheet
		if contains(thisSheetName, 'Results','IgnoreCase',true)
			continue;
		end
		numWorksheets = numWorksheets + 1;
		thisData = readmatrix(ds.Files{k}, 'Sheet', thisSheetName);
		% Get dates from the first row
		dates = thisData(1, 2:end);
		% Crop off first column and first row.
		thisData = thisData(2:end, 2:end);
		[rows, columns] = size(thisData);
		% Find last row of valid numbers before the nans start.
		lastRow = find(isnan(thisData(:, 2)), 1, 'first')-1;
		if isempty(lastRow)
			lastRow = rows;
		end
		% Extract MV
		mvWeights = thisData(1 : 3 : lastRow, :);
		% Extract returns
		AReturn = thisData(3 : 3 : lastRow, :);
		% Sum the weights in each column
		sumOfWeights = sum(mvWeights, 1); % Sum each column, going down rows.
		% Compute weighted sums for this one worksheet in this one workbook:
		fprintf('  Weighted Sums for worksheet :"%s"\n', thisSheetName)
		weightedAverage = sum(mvWeights .* AReturn, 1) ./ sumOfWeights

		% Write the data to the workbook
		rowHeaders = ['Dates'; repmat(rowHeader, numWorksheets, 1)]
		writecell(rowHeaders, thisFileName, 'Sheet', 'Results', 'Range', 'A1');
		writematrix(dates, thisFileName, 'Sheet', 'Results', 'Range', 'B1');
		cellID = sprintf('B%d', s+1);
		writematrix(weightedAverage, thisFileName, 'Sheet', 'Results', 'Range', cellID);
	end
end
% Open the workbook
if ispc
	winopen(thisFileName);
end
fprintf('Done!\n')

[ALEXANDRA]

Thank you so very much for everything!

[ALEXANDRA]

Hello,

I have some spreadsheets that look like the attached ('WA1'). The attached is just an example, in reality I have hundreds of data.

I have been trying to adapt the code by changing the lines 32-35 and say to it to continue calculating up to the end of ‘D’ in my example, but I have not achieved to do it and the calculations stop after ‘A’ because OK it stops when it finds the 1st empty line.

Is it possible something like that?

Thank you very much!

Answer 2

[Image Analyst]

Use readmatrix and tell it what sheet to read in. Then take your data and weights and do an element by element mutliplication and sum:

data = readmatrix(filename, 'sheet', 'whatever');
% Multiple each element of the 1st Row with each element of the 3rd Row 
v = data(1, :) .* data(3, :)
% and then divide by the sum of weights (in each column)
weights = I have no idea where to get these.  How are you getting the weights????
weightedMean = v ./ sum(weights)

If you have any more questions, then attach your data and code to read it in with the paperclip icon after you read this:

TUTORIAL: How to ask a question (on Answers) and get a fast answer

[ALEXANDRA]

Hello,

Thank you for your answer.

I cannot use the command readmatrix because my original sheets includes names and dates as well.

Attached you can find my file (just an example) along with the calculations.

I know how to do the calculations in Excel. This is not a problem. However, in this example I have very few data. For my project, I have hundreds of data and I cannot do all these calculations one by one and I am not familiar with Macros in Excel.

I am a little bit familiar with Matlab thus why I am trying to do it in Matlab.

Also, for my project, I do not have only one Sheet but my file includes several sheets (in my example I include only 2) and at the end I need to combine all my results in one Row or Column

Thank you