ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   Crash course (https://www.excelbanter.com/new-users-excel/454062-crash-course.html)

sjlarowe April 20th 18 08:32 PM

Crash course
 
3 Attachment(s)
Hello everyone, I've been using excel for a few years now, but until recently have really sat down and been getting help with formula's on this options trading sheet I've been working on.

It's purpose is to track trade statistics, and see how one performs over the year.

Attached I have 3 screenshots, the first is the transaction page, and the second is the summary page, third is the graph

On the first page the issue that I'm having is every blank row is listed with January, how can I make it be blank until I enter in a date? The formula in use is
=TEXT(C79, "MMMM")

On the second page, the layout is as followed

TOTAL TRADES uses
=COUNT('2018 Options Trades'!C:C)

GAIN uses
=COUNTIF('2018 Options Trades'!M:M, "=0")
LOSS uses
=COUNTIF('2018 Options Trades'!M:M, "<=0")

WIN/LOSS RATIO uses
=COUNTIF('2018 Options Trades'!M:M, "=0")/A2

GAIN AVERAGE uses
=AVERAGEIF(Table2[$ GAIN/LOSS], "0")
LOSS AVERAGE uses
=AVERAGEIF(Table2[$ GAIN/LOSS], "<0")

TOTAL GAIN uses
=SUMIF(Table2[$ GAIN/LOSS], "0")
TOTAL LOSS uses
=SUMIF(Table2[$ GAIN/LOSS], "<0")

GAIN/LOSS YTD uses
=(G2+H2)

CONTRACT QTY uses
=SUM('2018 Options Trades'!E2:E497)

COMMISSION uses
=SUM('2018 Options Trades'!L2:L497)

The things I'm lost with are on the second part of this, it's broken down by the months Win Rate/Net Profit.

The Net Profit I have calculated by
=SUMIF('2018 Options Trades'!D2:D18, "AAPL", '2018 Options Trades'!M2:M18)
This is done based on the specific of range of cells that had trades given in that specific month.

The problem I have with that is how can I have this auto calculate future trades without having to readjust any ranges?

On the Win Rate side, that's all manual

How can this be done automatically as well?
And then under the TOTAL section what formula can I use to make it determine the months win's over total trades and turn it into a percentage?

And finally I have a graph page

How can I set it so it only draws for data that has full info across the row and not the total 497 lines?

Thank you


All times are GMT +1. The time now is 11:55 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com