Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first excel experience (share portfolio)
hi guys
although i have had excel on my laptop for years this is the first time i have ventured to use it. i am trying to do a stock /share portfolio, so i can tell at a glance my positions, so far the layout as follows:- a6 buy/sell, input b6 amount buy,input c6 amount sell,input d6 name stock/share,input e6 epic code,input f6 buy date,input g6 sell date,input h6 purchase price per share,(bid)input i6 sell price per share,input j6 sub total [=h6*b6],calc k6 buy commission £,input l6 sell commission £,input m6 stamp duty tax,=((h6/100)*b6)*/0.5 ,calc n6 total cost =j6+k6+m6 ,calc o6 current price per share,input p6 current value, =06*b6 ,calc q6 sold profit/loss, =if(a6="sell"(c6*o6)-l6) ,calc r6 sold profit/loss%, =(q6/n6)-100% ,calc s6 dividend from share,input t6 dividend date,input u6 total profit/loss including dividend, =q6+s6 ,calc all the complicated (to me)formulas are what's been used from other spreadsheets found elsewhere, i am trying to get a formula for subtracting, for instance if after buying 400 shares in b6, sold 200 in c6 then i imagine the formula should go something like =if(a6="sell",(b6-c6), but that does not work for some reason.can anyone help please. on a not too important point,is it possible to have the live date and time displayed in a selected cell? and is anyone aware of portfolio template with more than the basic features that i can look at to compare with this one? thanks in advance for any help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first excel experience (share portfolio)
so far the layout as follows:-
a6 buy/sell, input b6 amount buy,input c6 amount sell,input ... in c6 then i imagine the formula should go something like =if(a6="sell",(b6-c6), but that does not work for some reason. I'd suggest starting with these headings for columns A, B, C. A1: buy/sell B1: transaction shares C1: cumulative shares Then in C2 put =IF(A2="buy",B2,"error") because the first transaction has to be a purchase. Then in C3 put =IF(A3="buy",C2+B3, IF(A3="sell", IF(B3C2,"error",C2-B3),"")) which gives a running share count by adding or subtracting as indicated by column A. Then copy C3 and paste into C4 downward. This formula can be used as a model for other calculations of this nature. is it possible to have the live date and time displayed in a selected cell? =NOW() anyone aware of portfolio template with more than the basic features ...? There are several to choose from he http://office.microsoft.com/ en-us/templates/results.aspx?qu=excel+portfolio&av=TPL000 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
my first excel experience (share portfolio)
On Tue, 17 Nov 2009 16:00:28 -0800 (PST), zvkmpw
wrote: so far the layout as follows:- a6 buy/sell, input b6 amount buy,input c6 amount sell,input ... in c6 then i imagine the formula should go something like =if(a6="sell",(b6-c6), but that does not work for some reason. I'd suggest starting with these headings for columns A, B, C. A1: buy/sell B1: transaction shares C1: cumulative shares Then in C2 put =IF(A2="buy",B2,"error") because the first transaction has to be a purchase. Then in C3 put =IF(A3="buy",C2+B3, IF(A3="sell", IF(B3C2,"error",C2-B3),"")) which gives a running share count by adding or subtracting as indicated by column A. Then copy C3 and paste into C4 downward. This formula can be used as a model for other calculations of this nature. is it possible to have the live date and time displayed in a selected cell? =NOW() anyone aware of portfolio template with more than the basic features ...? There are several to choose from he http://office.microsoft.com/ en-us/templates/results.aspx?qu=excel+portfolio&av=TPL000 many thanks for your help very impressed by =NOW() I should explain that my portfolio is based on the microsoft template http://office.microsoft.com/en-us/te...233521033.aspx which i have modified. i changed a1,b1,c1 to a,b,and c ref 5. my understanding is that the (a column remains a buy or sell),(c cumulative the total number shares bought),' (b' transaction shares comes into play as the number of shares in a sell transaction equal to or less than the number shares in dc).I also changed formula to reflect data entry starting at 6 instead of 2. added first formula as pasted =IF(A6="buy",B6,"error") thats ok. added 2nd formula as pasted =IF(A7="buy",C6+B7, IF(A7="sell", IF(B7C6,"error",C6-B7),"")) enter,ctrl/c paste to cells below, i find it odd that the cell references remain irrespective of the cells the formula is now pasted to. i am sure that i am doing something totally wrong but cant figure??????and the cumulative /transaction as opposed to amount buy amount sell has caused me a slight head scratching moment (peabrain moment) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Time in Excel - Frustating experience!! | Excel Discussion (Misc queries) | |||
Stock Portfolio Sample in excel | Excel Discussion (Misc queries) | |||
Looking for experience with counting and conditions in excel 2003 | Excel Worksheet Functions | |||
Convert excel spreadsheet application to a java or .Net Experience | Excel Discussion (Misc queries) | |||
Excel & 'Thin Client' experience anyone ? | Excel Discussion (Misc queries) |