my first excel experience (share portfolio)

B

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:-
c6 amount sell,input
d6 name stock/share,input
e6 epic code,input
g6 sell date,input
h6 purchase price per share,(bid)input
i6 sell price per share,input
j6 sub total [=h6*b6],calc
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

Z

zvkmpw

so far the layout as follows:-
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.
B1: transaction shares
C1: cumulative shares

Then in C2 put
because the first transaction has to be a purchase.

Then in C3 put
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

B

barbarebop

I'd suggest starting with these headings for columns A, B, C.
B1: transaction shares
C1: cumulative shares

Then in C2 put
because the first transaction has to be a purchase.

Then in C3 put
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

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