Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share Portfolio - Averaging the Cost per Shareholding
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share Portfolio - Averaging the Cost per Shareholding
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,C 100,F$4:F$1000) ,false) in column O =if(countif(C100:C$1000,C100)=1,Sumif(C$4:C$1000,C 100,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:D" & 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:D" & 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/sh...d.php?t=175872 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share Portfolio - Averaging the Cost per Shareholding
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 "joel" wrote: 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,C 100,F$4:F$1000) ,false) in column O =if(countif(C100:C$1000,C100)=1,Sumif(C$4:C$1000,C 100,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:D" & 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:D" & 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/sh...d.php?t=175872 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Share Portfolio - Averaging the Cost per Shareholding
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)" -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175872 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a total cost chart where a component cost is a step cost | Charts and Charting in Excel | |||
my first excel experience (share portfolio) | Excel Worksheet Functions | |||
Current Cost versus Original Cost | New Users to Excel | |||
Confusion on adding percentage of cost to that cost. | Excel Worksheet Functions |