I have a simple spread sheet that tracks stock purchases and sales. I want to calculate the correct profit or loss as I buy and sell the stock. I need to link the sale price of a share to the appropriate purchase price at the time it was purchased. For example:

A B C D

--------------------------

Purchases

Date Shares Price total

4/1 10 $20 $200

4/15 20 $25 $500

Sales

4/20 5 $30 $150

4/21 15 $40 $600

The first sale is 5 shares, so I want to measure profit against the first purchase price: i.e.: 5 shares were bought at $20 each and sold for $30 each = profit of $50.

Next I sell 15 shares @ $40, but the first 5 shares should be calculated as 5 shares bought @ $20 & sold for $40 = $100 profit. The remaining 10 shares were bought at $25 and sold at $40 = $150.

Is there any function that will help me calculate this for numerous purchases and sales?

Thanks

Larry

A B C D

--------------------------

Purchases

Date Shares Price total

4/1 10 $20 $200

4/15 20 $25 $500

Sales

4/20 5 $30 $150

4/21 15 $40 $600

The first sale is 5 shares, so I want to measure profit against the first purchase price: i.e.: 5 shares were bought at $20 each and sold for $30 each = profit of $50.

Next I sell 15 shares @ $40, but the first 5 shares should be calculated as 5 shares bought @ $20 & sold for $40 = $100 profit. The remaining 10 shares were bought at $25 and sold at $40 = $150.

Is there any function that will help me calculate this for numerous purchases and sales?

Thanks

Larry

- Calculate profit and Loss with Moving average using Macros/VBA
- Calculating profit and loss
- Profit and Loss Formula
- Profit and Loss Formula
- Excel formula for figuring Profit and loss
- Adding up profit and loss
- Profit and Loss
- Profit /loss calculations
- Calculating percentage of gain or loss
- Calculating Profit and Loss in one column
- Calculating Profit and Loss in one column
- Calculating Profit and Loss in one column
- Calculating gain loss with +or- sign
- Multiple Criteria (add or subtract)
- CALCULATE PROFIT/LOSS
- Income and profit/loss as a percentage
- Automatically calculate earnings per win?
- Excel formula to show my overall profit and loss.
- Profit/loss cell colouring formula
- Basic Macro - Auto calculate!
- Line or bar graphs for tracking stocks profit and loss.
- Gain or loss formula
- Breakeven formulas
- XL2000: Syntax Errors When Calculating Functions or Opening a Workbook

I have the following issue, thank you to read my post in advance.

I have the attached file, which is basically a set of stock prices over a certain period. Notice each stock price time gap is 30 min, not daily as usual (but I think it does not matter for what I am looking for). In column C and D I have the moving average period for long period (20 days) and short period (5 days) respectively. Column E is the Profit or Loss

The way it works is each time short period (column D) value is above the long period (Column C) value; I buy the stock at the corresponding price(column B) and I will sell it when the short period will be lower again than Long period at the corresponding price (column B)

See attached the excel file, and let me give you an example

For column C and D from row 21 to 44, Long Period is always higher than Short Period (so I do nothing) BUT on Row 45 Short Period is now above Long Period AND THIS until Row 68.

So what my macro needs to do is to automatically calculate B68-B45 = E45 with

(C68 the corresponding price at which the short period went above long period

C45 the corresponding price just before the short period returns below the long period) and this for each time the short period is above the long.

I put a graph also to have a visual view of what I am saying. each time the red line is above the blue line, the macro need to calculate the second point of cross minus the first, and so on.

I am very new in VBA, and would appreciate and help for you fellow member.

Thank you

I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.

I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.

Where:

Market position is either 1 or -1

Entry price is the price where the position is reversed.

For example:

H3=1 and I3=100.56, then

H4=-1 and I4=100.88, then

H5=1 and I5=100.53

I need to calculate the profit as follows:

I4 - I3 (because market position of 1 expects the price to go up) and then,

I4 - I5 (because market position of -1 expects the price to go down)

I want to calculate this as a running total to the bottom of the column.

This is the formula I was working with, but it doesn't work:

=IF(OR($J4="",ISERROR(MATCH(-$J4,$J4:$J$55,0)),),"",$J4*(OFFSET($K4,MATCH (-$J4,$J4:$J$55,0)-1,)-$K4))

Any help would be greatly appreciated.

Scott

ColA ColB ColC ColD ColE ColF

Bid Ask PL Side QTY TradePrice

12.7 12.8 -100 Sell 10 12.7

12.7 12.8 0 Buy 10 12.7

12.7 12.8 100 Sell 10 12.9

12.7 12.8 -200 Buy 10 12.9

ColD thru E has my trade data while ColA and ColB has the realtime market

prices. I am trying to come up with a formula for ColC which will calculate

my Profit or Loss for each trade.

For example on the last line, I bought 10 shares at 12.90. To close out the

position at that moment in time I would need to sell them on the "bid" side

at 12.70, thus creating a $200 loss.

I think the formula needs to look at the trade side. If the trade was a buy,

then the "bid" side of the realtime market is used in the Profit and Loss

Formula. If the trade price is greater than the "bid" price, then there is a

profit. If the trade price is less than the "bid" price, then there is a loss

- as in the example.

Similar but opposite for trade side = "sell". The formula will need to look

at the "ask" side of the real time market. If the trade price is greater than

the "ask" price, then there is a gain. If the trade price is less than the

ask price, there is a loss.

Thank you in advance.

ColA ColB ColC ColD ColE ColF

Bid Ask PL Side QTY TradePrice

12.7 12.8 -100 Sell 10 12.7

12.7 12.8 0 Buy 10 12.7

12.7 12.8 100 Sell 10 12.9

12.7 12.8 -200 Buy 10 12.9

ColD thru E has my trade data while ColA and ColB has the realtime market

prices. I am trying to come up with a formula for ColC which will calculate

my Profit or Loss for each trade.

For example on the last line, I bought 10 shares at 12.90. To close out the

position at that moment in time I would need to sell them on the "bid" side

at 12.70, thus creating a $200 loss.

I think the formula needs to look at the trade side. If the trade was a buy,

then the "bid" side of the realtime market is used in the Profit and Loss

Formula. If the trade price is greater than the "bid" price, then there is a

profit. If the trade price is less than the "bid" price, then there is a loss

- as in the example.

Similar but opposite for trade side = "sell". The formula will need to look

at the "ask" side of the real time market. If the trade price is greater than

the "ask" price, then there is a gain. If the trade price is less than the

ask price, there is a loss.

Thank you in advance.

A...................B

Sum....| Profit or loss (P/L)

10........| P

12........| L

I then want to add up all the 'P' items and all the 'L' items separately. I tried VLOOKUP, but it doesn't work.

How do I do it?

Ive been having issues working out a formula. I want it to do the following

Mon Tue Wed Thur Fri

100 200 350 200 300

(N/A) 100 150 150100

Basically i am going to enter a balance daily and need to enter the profit or loss for the day in the field below

I also need to pull through fridays figure into another sheet

Any ideas?

Thanks

In simple terms i have a column say D which each day may or may not generate a signal so a cell will have either Buy or Sell or be blank the data is ordered in date order. If a buy signel is shown i wish to take the closing price for that day column c as my entry price. At some point in the future there will be a sell signal (all blanks and additional buy signals to be ignored) at this point the closing price for at this point in column c will be taken as the closing of the trade and this price less the opening buy price calculated to give a profit or loss figure shown in column E. On the sell signal an new order is also generated at the clossing price and will be clossed when the next buy signal is generated. see table below.

A B C D E

Date opening P Clossing P Signal Profit/Loss

3/4/07 860.23 863.55

4/4/07 863.55 880.23 buy

5/4/07 880.23 881.45

6/4/07 881.45 879.30 sell 0.93

7/4/07 879.30 874.56

8/4/07 874.56 870.00

9/4/07 870.00 875.32 buy 3.98

10/4/07 875.32 880.00

It would mean a lot if someone could help thank you

I need to show this in a percentage representation either positive or negative. Also, some of the values are $0.00

Can anyone advise best way to achieve this?

Thanks

I am trying to total the profit column using this formula without any success:

=IF(OR($J3="",ISERROR(MATCH(-$J3,$J3:$J$1002,0)),),"",$J3*(OFFSET($K3,MATCH

(-$J3,$J3:$J$1002,0)-1,)-$K3))

Where a (1) means the purchase price subtracted from (-1) sales price and

the same (-1) is the sales price and the next (1) is the purchase price and

so on.

Calculating by hand would look like this:

101.125 - 100.531 = +.594 and,

101.125 - 94.750 = +6.375 and,

98.250 - 94.750 = +3.500

MARKET ENTRY PROFIT

POSITION PRICE

1 100.531

1

1

1

1

1

1

-1 101.125

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

1 94.750

1

1

-1 98.250

Any help would be greatly appreciated,

Regards,

Scott

I am trying to total the profit column using this formula without any success:

=IF(OR($J3="",ISERROR(MATCH(-$J3,$J3:$J$1002,0)),),"",$J3*(OFFSET($K3,MATCH

(-$J3,$J3:$J$1002,0)-1,)-$K3))

Where a (1) means the purchase price subtracted from (-1) sales price and

the same (-1) is the sales price and the next (1) is the purchase price and

so on.

Calculating by hand would look like this:

101.125 - 100.531 = +.594 and,

101.125 - 94.750 = +6.375 and,

98.250 - 94.750 = +3.500

MARKET ENTRY PROFIT

POSITION PRICE

1 100.531

1

1

1

1

1

1

-1 101.125

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

1 94.750

1

1

-1 98.250

Any help would be greatly appreciated,

Regards,

Scott

I am trying to total the profit column using this formula without any success:

=IF(OR($J3="",ISERROR(MATCH(-$J3,$J3:$J$1002,0)),),"",$J3*(OFFSET($K3,MATCH

(-$J3,$J3:$J$1002,0)-1,)-$K3))

Where a (1) means the purchase price subtracted from (-1) sales price and

the same (-1) is the sales price and the next (1) is the purchase price and

so on.

Calculating by hand would look like this:

101.125 - 100.531 = +.594 and,

101.125 - 94.750 = +6.375 and,

98.250 - 94.750 = +3.500

MARKET ENTRY PROFIT

POSITION PRICE

1 100.531

1

1

1

1

1

1

-1 101.125

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

-1

1 94.750

1

1

-1 98.250

Any help would be greatly appreciated,

Regards,

Scott

How can i get a + or - sign to show in my answer.

=IF(B3<B2,B2-B3,IF(B3>B2,B3-B2,""))

example: + $100.00

or - $100.00

Any help is appreciated

Thanks

Joel

paramaters like volume, change in price today vs change in price

yesterday, and price moving above or below a trading channel. This is

a back testing process working from historical data downloaded from the

yahoo stock site.

What would the best process be to quary all of the paramaters above and

when each is true issue a "Buy" signal and start accumilating "Profits"

untill the sheets hits a new criteria for "Sell".

I want to calculate the profit or loss based upon the change in stock

price between two sets of multiple criteria. Addationally I want the

sheet to issue one of three positions based upon the most current state

of the markers checked: Buy, Sell or Hold.

Not looking for specific answer just a point me to the correct excel

function.

Thanks, Michael

--

MJSlattery

------------------------------------------------------------------------

MJSlattery's Profile: http://www.excelforum.com/member.php...o&userid=16141

View this thread: http://www.excelforum.com/showthread...hreadid=378306

(more so than me) and could help, but this has opened up a bit of can of

worms in our office and would like some other input.

If we had a net loss the previous month of $-31,000 and this month we show a

net gain of $51,000.. what is the difference and is it a gain or loss? I say

that you take the negative $31000 and add it to the $51000 by that very

simple formula in Excel and you end up with a net gain between the 2 months

of $20,000... however, the others in my office are saying that you really end

up with $82,000... which means that you would have to take that negative

-31000 and subtract the 51000 in excel.. and then it's a negative (loss) of

the $82000... that doesn't seem right when you did end up this month with a

gain..

thanks for the help..

i am working out the total income of a contract minus the total costs to

show the contracts profit or loss.

how do i do this as a percentage]

ie: total income Â£19,213.68

total costs Â£10,274.11

Profit / (loss) Â£8,939.57

any help would be appreciated

Lisa

As you probably know by now I have set up several sheets to try and help me with the virtual game I play and I am trying to set another one up now.

I am wanting to calculate how much a horse has made profit or loss for each win it gets. So I want to calculate how much it made up to its first win, then from its first to its second win etc. I have attached a file so you can see more clearly what I am hoping to set up.

Thanks

Gemma

I am trying to have Excel show my Paypal overall profit and loss, so I believe I probably have to enter some formulas in Excel, but don't know how or where. I have a few purchases mixed in, payments received, there's a fees column for processing the credit cards, and there are also some refunds mixed in, not sure if this complicates things or not. I would also like to be able to see this month by month, perhaps I just customize the date range from Paypal before transferring.

I am making a share portfoilo spreadsheet,and want an easy viewing profit or loss coloured cell. if i am making a profit i want green and if making a loss make red

so (amount invested £10,000 * current price £2) -( amount invested £100* purchase price £1)

Profit = £10,000

Is there a way to set up a macro or button or something that will calculate my desired categories when needed without having to drag the formula down throughout the spreadsheet? Also, the macro would only be needed once the security is sold, therefore at times only columns A through C will be populated. Lastly, is it possible for the macro to work if some securities have completed their sale while others have not? For example, the security represented in row 5 is still open, while in row 12 has been sold and therefore should be included in profit/loss, cumulative total, and AUM columns.

Thanks a bunch!

Currently my spreadsheet has the following column headings:

A1: Security

B1: Date Purchased

C1: Quantity

D1: Purchase Price

E1: Date Sold

F1: Sale Price

G1: Profit or Loss

H1: Cumulative Total Profit/Loss

I1: AUM

I2: (my starting figure for AUM)

Ideally my transaction data would begin in row 3 then

graph is the best way but bar graphs are useful as well. However, I m not

able to find either in excel. Does anyone out there have any suggestions?

For example:

If cell D4 is greater than F4,the difference between the two cells has to show a negative answer in cell E4

If cell F4 is greater than cell D4, the difference between the two cells has to show a positive answer in cell E4

I need some help writing a few formulas in the attached spreadsheet. The Data_Input sheet where all the information will come from I have set-up Name ranges for each category.

I need to write the following formulas in the calculation sheet.

1 Fixed costs per week

2 Variable costs per drink in normal hours

3 Variable Costs per Drink in Overtime

4 The breakeven point in the number of 375ml drinks per week Inoring overtime

5 If the selling price per drinks changes, while all other data remains the same, what is the minimum selling price per drink below which overtime will be required to breakeven. I will need an If statement to test whether overtime is required.

6 A formula that calculates the profit or loss in normal hours

7 Graph_Data sheet will need formulas to allow me to graph it

8 I need to calculate the number of drinks that must be sold to achieve that target profit of $45,000.

9 A formular that will warn me that a target profit I keyed in will require more that 15 hours overtime.

Thank You for Your Help in advance

Runner77

When you recalculate values in a Microsoft Excel workbook that contains a custom function, the Microsoft Visual Basic Editor may be open and you may receive the following error ...

No luck finding an answer? You could always try Google.