my first excel experience (share portfolio)

[barbarebop]

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

 

[zvkmpw]

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(B3>C2,"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 here:
http://office.microsoft.com/
en-us/templates/results.aspx?qu=excel+portfolio&av=TPL000

 

[barbarebop]

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(B3>C2,"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.



There are several to choose from here:
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/templates/TC010233521033.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(B7>C6,"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)