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.

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

<tbody>
</tbody>

 

[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.