CODEWITHSUNDEEP
excelvbaformulavlookup
NHure92
NHure92

Reputation: 105

Excel vlookup/Macro to calculate trade data

I'll try and be as specific as I can while being mindful that the data is sensitive. I currently have a workbook which contains individual worksheets detailing each days trading activity, broken down by client name, stock traded etc. On the master worksheet within this workbook, I would like to have end of week/month trading figures summarised from each individual sheet/day.

So, for example: the master worksheet will show that in October, client x traded 500m volume of stock, by adding the figures on the individual sheets.

In order to do this, is there a vlookup I can implement in my master worksheet which will lookup a particular client name in each individual worksheet, sum the revenue for that client and add it on the master worksheet? Or would I need to go down the vba route? I understand its a rather vague request so any guidance would be much appreciated.

I've included a screenshot of the column labels to give some idea of the data:

Individual day worksheet

Upvotes: 0

Views: 142

Answers (1)

fabio.avigo
fabio.avigo

Reputation: 308

Given your sensitive information, I'll give you a generic answer that you can further adapt to your case.

You can use the SUMIFS function in excel as a VLOOKUP with multiple criteria, that may be suitable for this sort of problem. Let's say that your "Master" worksheet has only the fields Client Name and Date (month) on columns A and B, and you wish to fill the column C (Trade Volume) with the sum of monthly trades on your 'Trades' sheet, as in:

A           | B            | C
Client Name | Date (month) | Trade Volume
Bob         | 01-10-2018   | 500,000.00
Jack        | 01-11-2018   | 245,000.00

And one more condition, let's say your 'Trades' Sheet has the Client name on the first column (A), the Date of the given trade on the second column (B) and the volume of the trade on the third column (C)

You could use the following function on the cell C2 to achieve this sort of result:

=SUMIFS('Trades'!C:C, 'Trades'!A:A=A2, 'Trades'!B:B>= B2, 'Trades'!B:B<=EOMONTH(B2,0))

You may add more conditions to this formula, if necessary.

Upvotes: 0

Related Questions