Excel VBA - Updating hardcoded stock information using trade data via VBA

[12mcarchedi]

Hey guys,

I've been using this macro to copy and paste data that I have formatted myself from one column to another. In essence, if I make a stock trade today, it will copy the date, the net total of the trade, and the total value of my portfolio and hard code it within a list so the information will not change later. That macro looks like this:

Sub Trades()


    Dim wksCD As Worksheet                'reference to CD worksheet
Dim wksDates As Worksheet               'reference to Date worksheet
    Dim varDateToSearch As Variant       'holds search criteria, "0"
    Dim intSearchRow As Integer              'loops through rows to search
    Dim intNextRow As Integer                 'holds next destination row on Date worksheet
   


Application.ScreenUpdating = False
    
    'set references to worksheets
    Set wksCD = ThisWorkbook.Worksheets("CD")
Set wksDates = ThisWorkbook.Worksheets("Dates")

'store the Value to be searched
    varDateToSearch = 0
        'CD Trades
    'loop through the trade sheets, identifying matching rows
    intNextRow = wksCD.Cells(Rows.Count, "K").End(xlUp).Row + 1
    For intSearchRow = 22 To wksCD.Cells(Rows.Count, "C").End(xlUp).Row
        If wksCD.Range("L" & intSearchRow).Value <> "" Then
            If Abs(wksCD.Range("L" & intSearchRow).Value) > varDateToSearch Then
            wksCD.Range("K" & intSearchRow & ":M" & intSearchRow).Copy
            Range("c22:c100").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial xlPasteValues
            intNextRow = intNextRow + 1
            End If
        End If
    Next intSearchRow

WksDates.Select
    Range("a1").Select
    MsgBox "Trades Done"
    'Release the references
    Set wksDates = Nothing
    Set wksCD = Nothing

Application.ScreenUpdating = True
   
    
End Sub

The macro will search through the following table, find a row with a Abs(Buy/Sell value) greater than 0, and copy that row to another column.

This works very well, as long as I have only 1 trade that week. The problem is that I only run this macro once per week, and if I sell a stock on a Monday, my portfolio value will be incorrect for the trade on a Friday since I am not updating the hard coded stock information while the macro is going through the copy paste process. The data the macro copies is set up like this:


**This table is on sheet "CD" on range K20:M30**
[TABLE="class: grid, width: 306"]
<tbody>[TR]
[TD]Beginning date[/TD]
[TD]Buy/Sell[/TD]
[TD]Ending MV[/TD]
[/TR]
[TR]
[TD](Trading Date)[/TD]
[TD](On Begin Date)[/TD]
[TD]Trade T-1[/TD]
[/TR]
[TR]
[TD]1/20/2015[/TD]
[TD] $ -[/TD]
[TD] --[/TD]
[/TR]
[TR]
[TD]1/21/2015[/TD]
[TD] $ (87,222.81)[/TD]
[TD] $ 754,968.16[/TD]
[/TR]
[TR]
[TD]1/22/2015[/TD]
[TD] $ -[/TD]
[TD] $ 756,283.17[/TD]
[/TR]
[TR]
[TD]1/23/2015[/TD]
[TD] $ -[/TD]
[TD] $ 768,128.89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] $ -[/TD]
[TD] --[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] $ -[/TD]
[TD] --[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] $ -[/TD]
[TD] --[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] $ -[/TD]
[TD] --[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] $ -[/TD]
[TD] --[/TD]
[/TR]
</tbody>[/TABLE]

In this instance, I sold $87 K on Wednesday, January 21, with a total value of 754,968.16 before the trade happened. If I were to have another trade on the 22nd, the information wouldn't account for the sell on the previous day.

I input my trade data in the following table:

**This table is on sheet "Dates" on range A36:G38
[TABLE="class: outer_border, width: 774"]
<tbody>[TR]
[TD]Sector[/TD]
[TD]Ticker[/TD]
[TD]Trade Date[/TD]
[TD]Shares[/TD]
[TD]Buy/Sell[/TD]
[TD]Price[/TD]
[TD]Trade Total[/TD]
[/TR]
[TR]
[TD]CD[/TD]
[TD]DIS[/TD]
[TD]1/21/2015[/TD]
[TD]421[/TD]
[TD]Sell[/TD]
[TD]$207.22[/TD]
[TD]-$87,222.81[/TD]
[/TR]
[TR]
[TD]CD[/TD]
[TD]GPS[/TD]
[TD]1/22/2015[/TD]
[TD]421[/TD]
[TD]Buy[/TD]
[TD]$207.22[/TD]
[TD]$87,222.81[/TD]
[/TR]
</tbody>[/TABLE]


Which I would like to update this hard coded table before my macro moves onto the next line and copy and pastevalues the information.


**This table is on sheet "Dates" range J2:O12
[TABLE="class: grid, width: 681"]
<tbody>[TR]
[TD]Tickers[/TD]
[TD]Purchase Date[/TD]
[TD]Purchase Price[/TD]
[TD]Target Price[/TD]
[TD]Shares[/TD]
[TD]Px on PD[/TD]
[/TR]
[TR]
[TD]DIS[/TD]
[TD="align: right"]4/13/2012[/TD]
[TD] $ 41.85[/TD]
[TD] $ 105.01[/TD]
[TD="align: right"]1730[/TD]
[TD] $ 1,370.26[/TD]
[/TR]
[TR]
[TD]GPS[/TD]
[TD="align: right"]10/25/2013[/TD]
[TD] $ 36.63[/TD]
[TD] $ 51.91[/TD]
[TD="align: right"]2375[/TD]
[TD] $ 1,759.77[/TD]
[/TR]
[TR]
[TD]SNI[/TD]
[TD="align: right"]4/29/2013[/TD]
[TD] $ 66.86[/TD]
[TD] $ 91.48[/TD]
[TD="align: right"]1089[/TD]
[TD] $ 1,593.61[/TD]
[/TR]
[TR]
[TD]TJX[/TD]
[TD="align: right"]1/31/2011[/TD]
[TD] $ 23.75[/TD]
[TD] $ 68.00[/TD]
[TD="align: right"]1325[/TD]
[TD] $ 1,276.50[/TD]
[/TR]
[TR]
[TD]VLKAY[/TD]
[TD="align: right"]3/21/2014[/TD]
[TD] $ 48.17[/TD]
[TD] $ 53.74[/TD]
[TD="align: right"]1800[/TD]
[TD] $ 1,866.52[/TD]
[/TR]
[TR]
[TD]WHR[/TD]
[TD="align: right"]9/25/2014[/TD]
[TD] $ 150.89[/TD]
[TD] $ 212.20[/TD]
[TD="align: right"]715[/TD]
[TD] $ 1,965.99[/TD]
[/TR]
[TR]
[TD]XLY[/TD]
[TD="align: right"]12/15/2014[/TD]
[TD] $ 69.67[/TD]
[TD] $ 80.50[/TD]
[TD="align: right"]1570[/TD]
[TD] $ 1,886.76[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is there a way to integrate code that once the original macro encounters a row with a non-zero Buy/sell value, it will go back to the trade data input, find the date of the row just copied, take that information and update the share count on the hard coded stock information table?

I understand this is a very complicated explanation, but it seems like the best way to show what my problem is. It has bugged me for a while and I can't really understand how to do it. I am open to provide any additional information needed to help with the problem, but I'm just not sure what will be needed so I didn't include in on this post.

Any help is greatly appreciated and since you guys are trying to help I will try and respond ASAP so I'm not wasting anyone's time.

Thanks a lot,
MJC

 

[12mcarchedi]

Anyone out there understand my dilemma? I would love to get this fixed asap as its stopping me from moving on with my project.

Thanks,
MJC