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

[potterotter]

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

I want to use dictionaries to accomplish this.

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

Please Login or Register  to view this content.

Crossposted on: http://www.mrexcel.com/forum/excel-q...it-stocks.html


clerktestwithdictionary.xlsm

[AB33]

Because of these lines, the code will copy nothing.

Please Login or Register  to view this content.

I think you are calculating the values for each symbol by multiplying the price by the quantity. But I do not know how you got the value in column "Last Trade".
Could you explain a bit what are you trying to achieve?

[potterotter]

Because of these lines, the code will copy nothing.

Please Login or Register  to view this content.

I think you are calculating the values for each symbol by multiplying the price by the quantity. But I do not know how you got the value in column "Last Trade".
Could you explain a bit what are you trying to achieve?

Thanks for your reply!

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.

Please let me know if this is unclear! Thanks!

[AB33]

I do not know what is the relevance of Column B. I have only used column A, C, D and L to calculate the PL, which is P*Q.
If this is not what you wanted, you need to explain in more details.

[potterotter]

I do not know what is the relevance of Column B. I have only used column A, C, D and L to calculate the PL, which is P*Q.
If this is not what you wanted, you need to explain in more details.

Thanks for your reply!

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.

Thanks for writing a code. I would like to find out what is wrong with my existing code? I understand that the symbol = activesheet.cells(i,12) will not return anything but what can I do to rectify this? Or is my code completely wrong?

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

Thanks you so much!

[AB33]

The reason for writing my own code is that I do not understand your question.
Looking at your code, Column L (12) does not appear until the last lines, so where does it show you are comparing column against Column L?
You are correctly loading the data in to items (grossconsideration), but you are not using these items further down the lines.
The items plays important role if you are using dictionary.
You might not need to use 3 dictionaries. One or 2 would be suffice.

[potterotter]

Thanks! You're right! I used the wrong dictionary in the last bit. I changed it to stockdic now but I tried and it doesn't work.

Is it a syntax issue? I am not sure if I am properly storing and retrieving the items in the dictionary. Do I need to add a .value at the back?

This is my new code (I changed the last bit at the bottom). Thanks so much for your patience - I'm still learning.

Please Login or Register  to view this content.

[potterotter]

Thanks! You're right! I used the wrong dictionary in the last bit. I changed it to stockdic now but I tried and it doesn't work.

Is it a syntax issue? I am not sure if I am properly storing and retrieving the items in the dictionary. Do I need to add a .value at the back?

This is my new code (I changed the last bit at the bottom). Thanks so much for your patience - I'm still learning.

Please Login or Register  to view this content.

[HaHoBe]

Hi, potterotter,

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...it-stocks.html

Ciao,
Holger

[potterotter]

Thanks! I have edited the first post