Urgent help with debugging a dictionary code used to calculate profit in stocks

[potterotter]

I essentially want to calculate the profit/loss for each stock in column L.

I want to use dictionaries to accomplish this.

Essentially I was given data in column A,B,C, D, L and M.
I calculated column N using another macro as a checker.

I want to calculate the profit for the stocks in column L and place it in column O.
Column M (Last trade) is the last traded price of those stocks. Column C (Price) is the price at which the stock was bought.

The significance of column B is whether you are Buying (B) or Selling Short (S) a stock. If you are buying a stock then if the last traded price (Column M) is higher than the price bought (Column C) then you would make a profit. Conversely, if you were selling short a stock and the last trade price (M) is HIGHER than the price bought (C) you would LOSE money.

Sorry I am new to VBA and dictionaries so any help I can get will be much appreciated!

Does anyone know what's wrong with my code?
Thanks!

I'm new to this forum so please let me know if I'm breaking any rules!

[/COLOR]Sub usingdictionarytocalc()


Dim stockdic As Dictionary
Dim pricedic As Dictionary
Dim quantitydic As Dictionary
Dim r As Integer
Dim i As Integer
Dim stock As Variant
Dim direction As Variant
Dim price As Double
Dim quantity As Double
Dim grossconsideration As Double
Dim symbol As Variant
Dim totalnet As Double
Dim profit As Double


'set main dictionary to contain all other dictionaries
    Set stockdic = New Dictionary
'set price dic
    Set pricedic = New Dictionary
'set quantity dic
    Set quantitydic = New Dictionary
    
    r = 2
    
    stock = ActiveSheet.Cells(r, 1)
    direction = ActiveSheet.Cells(r, 2)
    price = ActiveSheet.Cells(r, 3)
    quantity = ActiveSheet.Cells(r, 4)
    
'keep processing until we run out of stock
    While Len(stock) > 0
'find out if there is a stock dictionary with the price already existing and if so, grab it
        If stockdic.Exists(stock) Then
            If direction = "B" Then
            grossconsideration = grossconsideration + (ActiveSheet.Cells(r, 3).Value * ActiveSheet.Cells(r, 4).Value)
            Else
            grossconsideration = grossconsideration - (ActiveSheet.Cells(r, 3).Value * ActiveSheet.Cells(r, 4).Value)
            stockdic(stock) = grossconsideration
            End If
        Else
'if its a new product then add it into the amount
            If direction = "B" Then
            grossconsideration = grossconsideration + (ActiveSheet.Cells(r, 3).Value * ActiveSheet.Cells(r, 4).Value)
            Else
            grossconsideration = grossconsideration - (ActiveSheet.Cells(r, 3).Value * ActiveSheet.Cells(r, 4).Value)
            stockdic.Add stock, grossconsideration
            End If
        End If
'create a quantity dictionary to track how much net stock we have to calculate profit later
        If quantitydic.Exists(stock) Then
            If direction = "B" Then
            quantity = quantity + ActiveSheet.Cells(r, 4)
            Else
            quantity = quantity - ActiveSheet.Cells(r, 4)
            End If
        Else
            If direction = "B" Then
            quantity = quantity + ActiveSheet.Cells(r, 4)
            Else
            quantity = quantity - ActiveSheet.Cells(r, 4)
            quantitydic.Add stock, quantity
            End If
        End If
        
            r = r + 1
            stock = ActiveSheet.Cells(r, 1)
            direction = ActiveSheet.Cells(r, 2)
            price = ActiveSheet.Cells(r, 3)
            quantity = ActiveSheet.Cells(r, 4)
            
    Wend
    
    'MsgBox stockdic("ABH")
    
    
    
'now to calculate profit
    i = 2


    While Len(symbol) > 0
        
'bring out the net quantity associated with the symbol
        totalnet = quantitydic(ActiveSheet.Cells(i, 12)) * ActiveSheet.Cells(i, 13)
        profit = totalnet - pricedic(ActiveSheet.Cells(i, 12))
        ActiveSheet.Cells(i, 15).Value = profit
        
        i = i + 1
     
    Wend
    




End Sub

This is a sample of what the data looks like because I can't seem to attach files here.

[TABLE="width: 956"]
<tbody>[TR]
[TD]SYMBOL[/TD]
[TD]DIRECTION[/TD]
[TD]PRICE[/TD]
[TD]QTY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Symbol[/TD]
[TD]Last Trade[/TD]
[TD]PNL[/TD]
[/TR]
[TR]
[TD]VRS[/TD]
[TD]B[/TD]
[TD="align: right"]1.94[/TD]
[TD="align: right"]53485[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABH[/TD]
[TD="align: right"]15.92[/TD]
[TD="align: right"]-315893.7026[/TD]
[/TR]
[TR]
[TD]WPP[/TD]
[TD]S[/TD]
[TD="align: right"]9.52[/TD]
[TD="align: right"]54249[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] BZ[/TD]
[TD="align: right"]8.29[/TD]
[TD="align: right"]764378.6152[/TD]
[/TR]
[TR]
[TD]BIP[/TD]
[TD]B[/TD]
[TD="align: right"]28.62[/TD]
[TD="align: right"]43796[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BIP[/TD]
[TD="align: right"]28.3[/TD]
[TD="align: right"]-232842.2168[/TD]
[/TR]
[TR]
[TD]VRS[/TD]
[TD]B[/TD]
[TD="align: right"]2.22[/TD]
[TD="align: right"]65725[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BKI[/TD]
[TD="align: right"]33.78[/TD]
[TD="align: right"]5612134.609[/TD]
[/TR]
[TR]
[TD] NP[/TD]
[TD]B[/TD]
[TD="align: right"]25.03[/TD]
[TD="align: right"]29841[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CLW[/TD]
[TD="align: right"]39.49[/TD]
[TD="align: right"]-386356.1992[/TD]
[/TR]
[TR]
[TD]MWS[/TD]
[TD]B[/TD]
[TD="align: right"]10.96[/TD]
[TD="align: right"]64095[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEL[/TD]
[TD="align: right"]69.89[/TD]
[TD="align: right"]12163310.95[/TD]
[/TR]
[TR]
[TD]MWS[/TD]
[TD]B[/TD]
[TD="align: right"]10.45[/TD]
[TD="align: right"]32003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]UFS[/TD]
[TD="align: right"]91.33[/TD]
[TD="align: right"]-456404.2553[/TD]
[/TR]
[TR]
[TD]BIP[/TD]
[TD]B[/TD]
[TD="align: right"]28.89[/TD]
[TD="align: right"]58415[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FBR[/TD]
[TD="align: right"]8.92[/TD]
[TD="align: right"]606513.6633[/TD]
[/TR]
[TR]
[TD]FBR[/TD]
[TD]S[/TD]
[TD="align: right"]9.78[/TD]
[TD="align: right"]14394[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GLT[/TD]
[TD="align: right"]15.8[/TD]
[TD="align: right"]-256974.1589[/TD]
[/TR]
[TR]
[TD] IP[/TD]
[TD]S[/TD]
[TD="align: right"]32.32[/TD]
[TD="align: right"]48051[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] IP[/TD]
[TD="align: right"]31.47[/TD]
[TD="align: right"]3462081.518[/TD]
[/TR]
[TR]
[TD] BZ[/TD]
[TD]S[/TD]
[TD="align: right"]8.90[/TD]
[TD="align: right"]19335[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MWS[/TD]
[TD="align: right"]10.45[/TD]
[TD="align: right"]-366544.5377[/TD]
[/TR]
[TR]
[TD]UFS[/TD]
[TD]B[/TD]
[TD="align: right"]91.65[/TD]
[TD="align: right"]26431[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] KS[/TD]
[TD="align: right"]18.07[/TD]
[TD="align: right"]1088408.453[/TD]
[/TR]
[TR]
[TD] BZ[/TD]
[TD]S[/TD]
[TD="align: right"]9.09[/TD]
[TD="align: right"]21222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] NP[/TD]
[TD="align: right"]24.81[/TD]
[TD="align: right"]-227964.5874[/TD]
[/TR]
[TR]
[TD]WPP[/TD]
[TD]S[/TD]
[TD="align: right"]9.53[/TD]
[TD="align: right"]19116[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SPP[/TD]
[TD="align: right"]3.41[/TD]
[TD="align: right"]481091.4638[/TD]
[/TR]
[TR]
[TD]CLW[/TD]
[TD]B[/TD]
[TD="align: right"]39.78[/TD]
[TD="align: right"]27476[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]VRS[/TD]
[TD="align: right"]1.38[/TD]
[TD="align: right"]-351248.9838[/TD]
[/TR]
[TR]
[TD]BIP[/TD]
[TD]B[/TD]
[TD="align: right"]28.60[/TD]
[TD="align: right"]23272[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]WPP[/TD]
[TD="align: right"]8.82[/TD]
[TD="align: right"]1254107.793[/TD]
[/TR]
[TR]
[TD]CLW[/TD]
[TD]B[/TD]
[TD="align: right"]39.56[/TD]
[TD="align: right"]18560[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABH[/TD]
[TD]B[/TD]
[TD="align: right"]16.85[/TD]
[TD="align: right"]25125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] BZ[/TD]
[TD]S[/TD]
[TD="align: right"]8.95[/TD]
[TD="align: right"]2124[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BIP[/TD]
[TD]B[/TD]
[TD="align: right"]28.83[/TD]
[TD="align: right"]45833[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD]S[/TD]
[TD="align: right"]70.26[/TD]
[TD="align: right"]85303[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] IP[/TD]
[TD]S[/TD]
[TD="align: right"]32.16[/TD]
[TD="align: right"]58421[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FBR[/TD]
[TD]S[/TD]
[TD="align: right"]9.27[/TD]
[TD="align: right"]89090[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] KS[/TD]
[TD]S[/TD]
[TD="align: right"]18.74[/TD]
[TD="align: right"]20368[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FBR[/TD]
[TD]S[/TD]
[TD="align: right"]9.71[/TD]
[TD="align: right"]17615[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UFS[/TD]
[TD]B[/TD]
[TD="align: right"]91.54[/TD]
[TD="align: right"]7873[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MWS[/TD]
[TD]B[/TD]
[TD="align: right"]11.21[/TD]
[TD="align: right"]85331[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FBR[/TD]
[TD]S[/TD]
[TD="align: right"]9.50[/TD]
[TD="align: right"]94035[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] KS[/TD]
[TD]S[/TD]
[TD="align: right"]18.63[/TD]
[TD="align: right"]39808[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VRS[/TD]
[TD]B[/TD]
[TD="align: right"]1.45[/TD]
[TD="align: right"]15598[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MWS[/TD]
[TD]B[/TD]
[TD="align: right"]10.94[/TD]
[TD="align: right"]18878[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WPP[/TD]
[TD]S[/TD]
[TD="align: right"]8.87[/TD]
[TD="align: right"]69795[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WPP[/TD]
[TD]S[/TD]
[TD="align: right"]9.10[/TD]
[TD="align: right"]92038[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UFS[/TD]
[TD]B[/TD]
[TD="align: right"]91.93[/TD]
[TD="align: right"]47684[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MWS[/TD]
[TD]B[/TD]
[TD="align: right"]10.93[/TD]
[TD="align: right"]19538[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABH[/TD]
[TD]B[/TD]
[TD="align: right"]16.47[/TD]
[TD="align: right"]70721[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FBR[/TD]
[TD]S[/TD]
[TD="align: right"]9.15[/TD]
[TD="align: right"]31790[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[VoG]

Cross posted Can someone please help me debug this dictionary code? I can't seem to get this to work

Please read Excelguru Help Site - A message to forum cross posters

 

[potterotter]

Cross posted Can someone please help me debug this dictionary code? I can't seem to get this to work

Please read Excelguru Help Site - A message to forum cross posters

Thanks for your post. I was not aware of the cross posting issue.

Yes I have not received a complete solution to my problem. I would like to learn from my mistakes so I hope someone can tell me what is wrong with my current code.

Thanks for your time.