I have my data like so:

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.

- Profit and Loss Formula
- Excel formula to show my overall profit and loss.
- Calculating profit and loss
- Help in inventory and profit and loss management
- Profit and loss sheet
- Line or bar graphs for tracking stocks profit and loss.
- Excel formula for figuring Profit and loss
- Profit and Loss
- Calculate profit and Loss with Moving average using Macros/VBA
- Excel profit and loss help
- Adding up profit and loss
- Calculating Profit and Loss in one column
- Calculating Profit and Loss in one column
- Calculating Profit and Loss in one column
- Formulas Causing Spreadsheet To Run Slow
- Calculate Profit Or Loss
- Sum a series and find lowest value in it
- Oponents win and losses and Strength of Schedule $30
- Avg and iserror formula
- Formula Refreshing
- Looking for help building an excel formula
- Profit & loss
- Streak on Wins and Losses
- Monthly Profit/Loss

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.

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 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

I want to do an inventory system but I want it to automatically decrease the inventory stock when I increase the stock at Profit and loss side.

Please do take a look at the excel sheet attached.

Thank you :-) and I appreciate any help given.

I attached my stock transactions sheet (Sheet1).

I need macro that converts this data as per sheet2 ,that is my profit and loss statments.

Regards

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?

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

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

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?

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

I'm not new to excel, but complexed formulas is certainly not my forte and I have no idea on how to speed it up, considering im just in the styling stage of setting up the next financial years profit and loss sheet.

So what i've got if anybody is able to assist is this:

I downloaded a 'profit & loss statement' file and have modified it to meet my business needs, and what i've done is added 2 additional worksheets, one being setup (which includes dates mainly) and the 2nd being a 'ledger' where I enter all the years information in (like purchases, sales etc).

What the program/file is to allow is for the user (me) to enter details of sales, purchases etc into the 'ledger' form, and then when I want to view sales, expenditure etc etc, I go to the profit & loss form, which is categorised by each month.

Now the formula i'm using is the '=sumproduct' formula. Currently, I believe this is what is causing the file to run exceptionally slow, and all I have in there so far is just sample data for several months to ensure the formulas were working correctly.

Now the other thing that also appeared is when I added more formulas (have since deleted them), an error message kept appearing saying out of memory or something.

Also whether this means anything or not, but im using a 1month old computer that is an I7, so its certainly not a slow or old computer.

Any help is greatly appreciated, as I find for a small business like mine, its just too expensive to get MYOB or other accounting software.

attached is the file. (the zip file is the .xls, and the other is the office 2007 version), same file, just saved differently for those that might not have office 2007.

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

I start off with $10,000 (C2) and you can see the amount that one has at the end of the trading period in C10. It is nothing but adding up profits and losses throughout the trading period from 7/30/2008 to 8/19/2008. E.g. cell D2 displays the total amount (after profit/loss) if one starts trading on 7/30/2012 with $10,000 in pocket. In cell E2 you can see the lowest level of money one has throughout the period.

What I'd like to do is to create some kind of formula/macro that adds similar numbers for rows D3 to E10. i.e. what is the total at the end of the period and lowest amount if one starts trading with $10000 on 7/31. That information should populate automatically in cell D3 & E3. The same way what is the total at the end of the period and lowest amount if one starts trading with $10000 on 08/01. That information should populate automatically in cell D4 & E4.

Don't have any clue in the world how to do that?

Can someone help please?

Appreciate your help in advance. Thanks.

I have a spreadsheet I have created to calculate rankings. I have figured out how to look up the teams wins and losses. Now I need a way to figure out how to look up all of the oponents wins and losses. The sheet is pulling all data from a score page sheet where the scores of games are entered. I now need to calculate oponents wins and losses that they have currently played.

The second peice is Strength of Schedule and Opponents Strenght of Schedule.

I would like to use current rankings as a strength adder. The last place team will be assigned a 1 counting up to the top ranked team. When a team plays a home game against the lowest ranked team, it will add a 1 (opponents strength adder) to the teams strength of schedule pool. If a team plays an away game it will be the teams I will add 1.5 times the opponents strength adder to the teams schedule strength pool. Then the team with the highest pool with have the hardest strength of schedule. as games are entered I would like the formula to change with the rankings of the teams

Payment is $30 USD

I have 3 columns.... Wins Column (B), Losses Column (C) and Win Pct Column (D)...

I've calculated the Win Pct with this formula: B22/SUM(B22:C22)

I have some players that have not played yet, and since their wins and losses are 0 & 0, the Win Pct column gives a result of #Div/0!....

I print these stats pages out every week and would prefer not to have the error message displayed but rather NA or something similar...

How would I utilize the avg and iserror formula together to get rid of this error? Or is there another way to do this?

Erik

spreadsheet set up for a month by month profit and loss that uses formulas to

calculate subtotals/totals/balances both accross and down for me.

However when I enter or edit information into the cells that the formulas

relate to, the totals do not update, it isn't until I actually edit and then

accept the formula cell afterwards that the updated value appears.

If I happen to miss 'updating' the formula in one cell then all other

balances remain as were previously calculated. This is quite frustrating and

I'm sure there is just an option or something that I'm missing somewhere.

Please help.

I have been working on making formula on streak on wins and losses on my excel for football statistics. The formula I am using is:

"=(INDEX(G10:G26,COUNT(H10:H26))&(INDEX(H10:H26,COUNT(H10:H26))))"

The result came out as L4 (lost 4 straight games), that result is just what I want, but the problem is when I put in the final score on next game, the result came out blank. I couldn't figure the problem.

Thanks

This is my first thread.

I search on google/youtube but unable to find solution, so I am asking here.

I want to calculate profit/loss of every month and show it in other box.

Like what would be Jan profit and loss, feb profit and loss, ..............

I am attaching a file.

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