Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 20th 18, 08:32 PM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 1
Default Crash course

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'!D218, "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
Attached Images
   

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VB Crash - Little Bit Of Fun RajenRajput1 Excel Discussion (Misc queries) 4 May 21st 07 02:35 PM
Crash martin Excel Discussion (Misc queries) 2 July 13th 05 09:36 PM
Crash upon using RefEdit Oliver Kharraz Excel Programming 3 November 18th 03 05:36 PM
Crash on every 2nd run Christof[_4_] Excel Programming 1 September 29th 03 08:50 PM
Crash on every 2nd run Christof[_4_] Excel Programming 0 September 29th 03 07:57 AM


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

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017