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

12mcarchedi

New Member
Joined
Jan 19, 2015
Messages
8
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:

Code:
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**
Beginning dateBuy/SellEnding MV
(Trading Date)(On Begin Date)Trade T-1
1/20/2015 $ - --
1/21/2015 $ (87,222.81) $ 754,968.16
1/22/2015 $ - $ 756,283.17
1/23/2015 $ - $ 768,128.89
$ - --
$ - --
$ - --
$ - --
$ - --

<tbody>
</tbody>

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
SectorTickerTrade DateSharesBuy/SellPriceTrade Total
CDDIS1/21/2015421Sell$207.22-$87,222.81
CDGPS1/22/2015421Buy$207.22$87,222.81

<tbody>
</tbody>


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
TickersPurchase DatePurchase PriceTarget PriceSharesPx on PD
DIS4/13/2012 $ 41.85 $ 105.011730 $ 1,370.26
GPS10/25/2013 $ 36.63 $ 51.912375 $ 1,759.77
SNI4/29/2013 $ 66.86 $ 91.481089 $ 1,593.61
TJX1/31/2011 $ 23.75 $ 68.001325 $ 1,276.50
VLKAY3/21/2014 $ 48.17 $ 53.741800 $ 1,866.52
WHR9/25/2014 $ 150.89 $ 212.20715 $ 1,965.99
XLY12/15/2014 $ 69.67 $ 80.501570 $ 1,886.76

<tbody>
</tbody>


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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

12mcarchedi

New Member
Joined
Jan 19, 2015
Messages
8
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,863
Messages
5,766,814
Members
425,379
Latest member
thedoctor00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top