Easy Excel Question

[ghuughes93]

Hi everybody. New to this forum.

I just have a really quick question about Class Modules. The only problem is I am clueless with Excel and have a deadline for tomorrow to do this exercise for.

I have about 5000 rows of data like this:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Direction[/TD]
[TD="width: 64"]Quantity[/TD]
[TD="width: 64"]Price[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]12.75[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]19.53[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]38.34[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]9.56
[/TD]
[/TR]
</tbody>[/TABLE]

B = buy and S = sell. I need to work out the total value. If the direction is buy (i.e. you're spending money) it should be a minus figure. If it's sell (i.e. you're gaining money) it should be a positive figure.

The cash associated with every row is Quantity*Price. So for the first one it's -(400*12.75) and for the third its 900*9.56. I need to output the total net cash (sum of the cash) from the trades in the spreadsheet in a Message Box.

I can't tell you how stuck I am right now and would greatly greatly appreciate any help.

Thank you in advance if anyoncan give me any direction!

 

[ghuughes93]

The thing that's throwing me most here is that some of the row product will be negative and some will be positive depending on whether it's buy or sell respectively :s

 

[Alex0013]

Hello,

This should work:

Sub NetCash()
    Dim runningTotal As Double
    Dim direction As Range
    Set direction = Range("A:A")
    
    For Each cell In direction
        If cell = "B" Then
            runningTotal = runningTotal - cell.Offset(0, 1) * cell.Offset(0, 2)
        ElseIf cell = "S" Then
            runningTotal = runningTotal + cell.Offset(0, 1) * cell.Offset(0, 2)
        End If
    Next cell
    
    MsgBox "Net Cash is: " & Format(runningTotal, "Currency")


End Sub

Regards,
Alex

 

[ghuughes93]

Also worth mentioning that it said this:



Although there may be other ways to accomplish the task, please create a Class Module called Trade that represents and contains the data for a trade. Write a function in the class module that outputs the cash from the trade. The function should look like:

Function cash_of_trade() Double

 

[MWANGI]

Hi Ghuughes,

Create the third Column for cash and below the column type below formula
=sumIFS(C1:Cn,A1:An,"S")-sumIFS(c1:cn,A1:An,"B")

Assuming your cash column is column C and Direction Column is A and n represents the last row in your data

 

[ghuughes93]

Hello,

This should work:

Sub NetCash()
    Dim runningTotal As Double
    Dim direction As Range
    Set direction = Range("A:A")
    
    For Each cell In direction
        If cell = "B" Then
            runningTotal = runningTotal - cell.Offset(0, 1) * cell.Offset(0, 2)
        ElseIf cell = "S" Then
            runningTotal = runningTotal + cell.Offset(0, 1) * cell.Offset(0, 2)
        End If
    Next cell
    
    MsgBox "Net Cash is: " & runningTotal


End Sub

Regards,
Alex

Hi Alex, thanks for your quick reply.

I annot explain how inexperienced I am with Macros. So do I write this in a public Class Module then?

Does your code also take into account that "Direction" is in column A, "Quantity" is B and "Price" is C?

Gareth

 

[ghuughes93]

Hi Ghuughes,

Create the third Column for cash and below the column type below formula
=sumIFS(C1:Cn,A1:An,"S")-sumIFS(c1:cn,A1:An,"B")

Assuming your cash column is column C and Direction Column is A and n represents the last row in your data

Hi MWANGI. I was told I cannot edit the spreadsheet in any way at all.

This is the precise message I was given if it helps:

To test your ability to learn and write VBA please do the following exercise:

The basic data that describes a trade in a stock are:


- direction (buy or sell)
- quantity (number of shares)
- price (in US dollars)


The cash associated with a trade is the price * quantity. Positive if we sell, negative if we buy.


Please write VBA code in the attached spreadsheet containing data describing a bunch of trades (possibly thousands) and output the total net cash (sum of the cash) from the trades in the spreadsheet in a Message Box.
The final solution should not use any Excel formulas, the cells of the spreadsheet shouldn't contain anything but what I've sent you.


Although there may be other ways to accomplish the task, please create a Class Module called Trade that represents and contains the data for a trade. Write a function in the class module that outputs the cash from the trade. The function should look like:


Function cash_of_trade() Double


If you get stuck, it may be easier to try to write the spreadsheet without using the Trade Class Module and function and then add them back in afterward.

 

[ghuughes93]

Hello,

This should work:

Sub NetCash()
    Dim runningTotal As Double
    Dim direction As Range
    Set direction = Range("A:A")
    
    For Each cell In direction
        If cell = "B" Then
            runningTotal = runningTotal - cell.Offset(0, 1) * cell.Offset(0, 2)
        ElseIf cell = "S" Then
            runningTotal = runningTotal + cell.Offset(0, 1) * cell.Offset(0, 2)
        End If
    Next cell
    
    MsgBox "Net Cash is: " & Format(runningTotal, "Currency")


End Sub

Regards,
Alex

I'm just entering it into a Class Module that I've created which is public and then clicking enter. Is that not the corect way to execute this?

Thanks again for your help so far!

 

[ghuughes93]

Also if it's that simple Alex, where does the

Function cash_of_trade() Double

bit come into it?!?! :s

Thanks,
Gareth

 

[iyyi]

Also:
Sub TotSum()
MsgBox "Net Cash is: " & [SUM(B2:B5*C2:C5*IF(A2:A5="S",1,-1))]
End Sub