Share Portfolio - Averaging the Cost per Shareholding

[Chris Maddogz]

I have a portfolio of shares stored in an Excel Workbook called Super 2
Etrade Contract Layout - the worksheet is called Super2 Holdings.
I need a macro to help me work out the average cost of each share held.

The Stock Codes (in Alpha order) are in Column C starting at C4.

The Quanitity per trade is is in Column F starting at F4.

The Total Cost per trade is in K starting at K4

I need to accumulate the Total Quantity per Stock and Total Cost per Stock
into Column N & O respectively on the row of the last entry for the Stock -
then divide that Total Cost by Total Quantity and put the result into Column
P of the last entry for that stock.

Then move onto the next code and continue the calcluation until until there
are no entries in Column C (ie the Stock Code in Column C is blank)

 

[joel]

You have 4 methods to solve this problem. Basically all of them are th
same

1) Put a formula into the worksheet manually.
2) Put a countif formula using a macro
3) Use methods 1 and 2 then remove the formula by doing a copying an
Pastespecial using the property Values which will remove the formula
4) Use a worksheetfunction,countif in VBA and put the totals into th
worksheet so you don't end up wit a formula on the workbook. Putting
lot of formulas into a workbook can slow down a work significantl
especially when you open a large workbook.

Look at chip Pearson webpage for unusal formulas

'CPearson.com Topic Index' (http://www.cpearson.com/Excel/topic.aspx)


Do get the last occrance of a stock on the owrksheet use countif wit
an IF statment

Put this formula in row 100
=if(countif(C100:C$1000,C100)=1,true,false)

Notice the dollar signs. As you copy the formula down a column th
100 wil change and you will only get a true when the last time th
stoock name will appear on the worksheet.

the total quantity can be found with a SumIF formula in column N

=Sumif(C$4:C$1000,C100,F$4:F$1000)

The Total Cost per trade in column O

=Sumif(C$4:C$1000,C100,K$4:K$1000)

the division in colum P

=N100/O100

The final formulas combining the counntif with the other formulas woul
look like this

in column N
=if(countif(C100:C$1000,C100)=1,Sumif(C$4:C$1000,C100,F$4:F$1000)
,false)
in column O
=if(countif(C100:C$1000,C100)=1,Sumif(C$4:C$1000,C100,K$4:K$1000)
,false)
in column P
=if(countif(C100:C$1000,C100)=1,N100/O100,false)


Now in VBA do this. Notice I changed the formulas to put them in ro
4. then examples above were in row 100 to make it easy to understand.
I've done this onlu for the first formula


LastRow = Range("C" & rows.count).end(xlup).row
'notice I made the fromula a string by putting double quotes around th

'formula
Range("N4").formula = "=if(countif(C4:C$1000,C4)=1," & _
Sumif(C$4:C$1000,C4,F$4:F$1000),false)"

'Now copy formula down the column
Range(N4").copy destination:=Range("N4:N" & Lastrow)

'to remove the formulas
Range("N4:N" & Lastrow).copy
Range("N4").pastespecial paste:=xlpastevalues


Now to method 4

Sub test()

Lastrow = Range("C" & Rows.Count).End(xlUp).Row
'get last occurance
For RowCount = Lastrow To 4 Step -1
StockName = Range("C" & RowCount)
'check if lat time stock occurred
Set CountRange = Range("C" & RowCount & ":C" & Lastrow)
If WorksheetFunction.CountIf(CountRange, StockName) = 1 Then
Range("N" & RowCount).Formula = _
"=sumif(C4:C" & Lastrow & ",C:" & RowCount & _
",F4" & Lastrow & ")"
End If
Next RowCount

End Sub


'now if you don't want the formula left in the worksheet replace the I
block with this

If WorksheetFunction.CountIf(CountRange, StockName) = 1 Then
Range("N" & RowCount) = _
Evaluate("sumif(C4:C" & Lastrow & ",C:" & RowCount & _
",F4" & Lastrow & ")")
End If


Notice I removed the equal sign and put the function in an evaluate.
Another method for doing the same thing not using the evaluate


If WorksheetFunction.CountIf(CountRange, StockName) = 1 Then
Set StockRange = Range("A" & RowCount & ":A" & Lastrow)
Set SumRange = Range("F" & RowCount & ":F" & Lastrow)
Range("N" & RowCount) = worksheetfunction.Sumif( _
StockRange,StockName,Sumrange)
End I

 

[Chris Maddogz]

Thanks Joel I think the VBA version is the way to go as I have multiple
Portfolio spreadsheets and would like to use the macro elsewhere.
As a very minor user of VBA I copied your original code from Method 4 and
replaced the section from If WorksheetFunction with your final piece of code
but when I ran the code it ended up with zeros in Column N for each last line
for a stock.
There were also blanks in the associated Column O (the Total Value cell and
P (the Average Price) cell but I'm not sure the code you gave also performed
those calculations.
As a very basic user in VBA I am pretty sure I am missing something but your
help is very much appreciated as I can see how close the solution is by what
you are trying to achieve for me

You have 4 methods to solve this problem. Basically all of them are the
same

1) Put a formula into the worksheet manually.
2) Put a countif formula using a macro
3) Use methods 1 and 2 then remove the formula by doing a copying and
Pastespecial using the property Values which will remove the formula
4) Use a worksheetfunction,countif in VBA and put the totals into the
worksheet so you don't end up wit a formula on the workbook. Putting a
lot of formulas into a workbook can slow down a work significantly
especially when you open a large workbook.

Look at chip Pearson webpage for unusal formulas

'CPearson.com Topic Index' (http://www.cpearson.com/Excel/topic.aspx)


Do get the last occrance of a stock on the owrksheet use countif with
an IF statment

Put this formula in row 100
=if(countif(C100:C$1000,C100)=1,true,false)

Notice the dollar signs. As you copy the formula down a column the
100 wil change and you will only get a true when the last time the
stoock name will appear on the worksheet.

the total quantity can be found with a SumIF formula in column N

=Sumif(C$4:C$1000,C100,F$4:F$1000)

The Total Cost per trade in column O

=Sumif(C$4:C$1000,C100,K$4:K$1000)

the division in colum P

=N100/O100

The final formulas combining the counntif with the other formulas would
look like this

in column N
=if(countif(C100:C$1000,C100)=1,Sumif(C$4:C$1000,C100,F$4:F$1000)
,false)
in column O
=if(countif(C100:C$1000,C100)=1,Sumif(C$4:C$1000,C100,K$4:K$1000)
,false)
in column P
=if(countif(C100:C$1000,C100)=1,N100/O100,false)


Now in VBA do this. Notice I changed the formulas to put them in row
4. then examples above were in row 100 to make it easy to understand.
I've done this onlu for the first formula


LastRow = Range("C" & rows.count).end(xlup).row
'notice I made the fromula a string by putting double quotes around the

'formula
Range("N4").formula = "=if(countif(C4:C$1000,C4)=1," & _
Sumif(C$4:C$1000,C4,F$4:F$1000),false)"

'Now copy formula down the column
Range(N4").copy destination:=Range("N4:N" & Lastrow)

'to remove the formulas
Range("N4:N" & Lastrow).copy
Range("N4").pastespecial paste:=xlpastevalues


Now to method 4

Sub test()

Lastrow = Range("C" & Rows.Count).End(xlUp).Row
'get last occurance
For RowCount = Lastrow To 4 Step -1
StockName = Range("C" & RowCount)
'check if lat time stock occurred
Set CountRange = Range("C" & RowCount & ":C" & Lastrow)
If WorksheetFunction.CountIf(CountRange, StockName) = 1 Then
Range("N" & RowCount).Formula = _
"=sumif(C4:C" & Lastrow & ",C:" & RowCount & _
",F4" & Lastrow & ")"
End If
Next RowCount

End Sub


'now if you don't want the formula left in the worksheet replace the IF
block with this

If WorksheetFunction.CountIf(CountRange, StockName) = 1 Then
Range("N" & RowCount) = _
Evaluate("sumif(C4:C" & Lastrow & ",C:" & RowCount & _
",F4" & Lastrow & ")")
End If


Notice I removed the equal sign and put the function in an evaluate.
Another method for doing the same thing not using the evaluate


If WorksheetFunction.CountIf(CountRange, StockName) = 1 Then
Set StockRange = Range("A" & RowCount & ":A" & Lastrow)
Set SumRange = Range("F" & RowCount & ":F" & Lastrow)
Range("N" & RowCount) = worksheetfunction.Sumif( _
StockRange,StockName,Sumrange)
End If


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=175872

Microsoft Office Help

.

 

[joel]

There were two minor typos in one statment

Range("N" & RowCount).Formula = _
"=sumif(C4:C" & Lastrow & ",C" & RowCount & _
",F4:F" & Lastrow & ")"


I had D: and replaced with just C.


Here are the other two forumulas

Range("O" & RowCount).Formula = _
"=sumif(C4:C" & Lastrow & ",C" & RowCount & _
",K4:K" & Lastrow & ")"

Range("P" & RowCount).Formula = _
"=Range("N" & RowCount) / Range("O" & RowCount)"