Trading metrics spreadsheet for multiple entries/exits? - futures io
futures io



Trading metrics spreadsheet for multiple entries/exits?


Discussion in Psychology and Money Management

Updated
    1. trending_up 2,303 views
    2. thumb_up 4 thanks given
    3. group 2 followers
    1. forum 6 posts
    2. attach_file 1 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 125,000 members
  • Genuine reviews from real traders, not fake reviews from stealth vendors
  • Quality education from leading professional traders
  • We are a friendly, helpful, and positive community
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts
  • We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

(If you already have an account, login at the top of the page)

 
Search this Thread
 

Trading metrics spreadsheet for multiple entries/exits?

(login for full post details)
  #1 (permalink)
 swinger 
Raleigh NC/USA
 
Experience: Beginner
Platform: NinjaTrader
Trading: stocks
 
Posts: 32 since Oct 2014
Thanks: 183 given, 15 received

Anyone have ideas on tracking trading metrics in a spreadsheet for trades that involve multiple entries and exits (and for which exits may not correspond to a particular entry - e.g., when your system triggers a buy, you may buy in 2 lots separated in time, and when you sell, you may sell in 3 lots)?

I'm thinking of adding columns to my transaction tracking spreadsheet that will allow me to tie the various entries and exits that correspond to a single trade idea/signal and let me consolidate metrics into a pivot table.

The ultimate goal is effective journaling and getting a handle on how the trade ideas are working, rather than getting lost in the line by line minutiae of individual entry and exit trades.

Started this thread Reply With Quote

Can you help answer these questions
from other members on futures io?
Simple code to end my indicator
NinjaTrader
Advice on SetBreakeven Exit on daily bars
TradeStation
Problem with Compare feature in TradingView
Platforms and Indicators
RangeRider for NT8 V10
The Elite Circle
Order cumulation
TradeStation
 
Best Threads (Most Thanked)
in the last 7 days on futures io
Vinny E-Mini & Algobox Review TRADE ROOM
268 thanks
Would you share your Holy Grail?
60 thanks
EURUSD M6E/6E Euro
59 thanks
Spoo-nalysis ES e-mini futures S&P 500
47 thanks
TST/OneUp/LeeLoo/Earn2Trade
24 thanks
 
(login for full post details)
  #3 (permalink)
 xplorer 
Site Moderator
London UK
 
Experience: Beginner
Platform: CQG
Broker: S5
Trading: Futures
 
xplorer's Avatar
 
Posts: 5,491 since Sep 2015
Thanks: 13,963 given, 13,664 received



swinger View Post
Anyone have ideas on tracking trading metrics in a spreadsheet for trades that involve multiple entries and exits (and for which exits may not correspond to a particular entry - e.g., when your system triggers a buy, you may buy in 2 lots separated in time, and when you sell, you may sell in 3 lots)?

I'm thinking of adding columns to my transaction tracking spreadsheet that will allow me to tie the various entries and exits that correspond to a single trade idea/signal and let me consolidate metrics into a pivot table.

The ultimate goal is effective journaling and getting a handle on how the trade ideas are working, rather than getting lost in the line by line minutiae of individual entry and exit trades.

Several ways to skin that cat.... do you have a specific design in mind, or do you already have a base Excel sheet you want to develop?

Reply With Quote
 
(login for full post details)
  #4 (permalink)
 swinger 
Raleigh NC/USA
 
Experience: Beginner
Platform: NinjaTrader
Trading: stocks
 
Posts: 32 since Oct 2014
Thanks: 183 given, 15 received


xplorer View Post
Several ways to skin that cat.... do you have a specific design in mind, or do you already have a base Excel sheet you want to develop?

I have a base spreadsheet that has entry and exit transactions (each transaction is one row). It's a Google Sheets spreadsheet that someone else developed: Dividend Stock Portfolio Tracker with Transactions Page ? Two Investing

It's not intended for active trading, and especially not day trading, but I find it useful for capturing position values and metrics like gain/loss, etc., as well as sector weightings and portfolio values (for positions in different accounts) for longer term trading (e.g., swing trading).

I'm thinking of adding columns to the transactions sheet that tie the various entry and exit trades to a single trade as well as contain data that can be used in a pivot table to summarize performance (gain/loss, MAE, MFE, etc.) on a particular trade idea (a decision to go long/short a particular security at/around a particular time based on a particular setup), which may compromise multiple entries and exits.

Open to ideas!

Started this thread Reply With Quote
 
(login for full post details)
  #5 (permalink)
 xplorer 
Site Moderator
London UK
 
Experience: Beginner
Platform: CQG
Broker: S5
Trading: Futures
 
xplorer's Avatar
 
Posts: 5,491 since Sep 2015
Thanks: 13,963 given, 13,664 received


swinger View Post
I have a base spreadsheet that has entry and exit transactions (each transaction is one row). It's a Google Sheets spreadsheet that someone else developed: Dividend Stock Portfolio Tracker with Transactions Page ? Two Investing

It's not intended for active trading, and especially not day trading, but I find it useful for capturing position values and metrics like gain/loss, etc., as well as sector weightings and portfolio values (for positions in different accounts) for longer term trading (e.g., swing trading).

I'm thinking of adding columns to the transactions sheet that tie the various entry and exit trades to a single trade as well as contain data that can be used in a pivot table to summarize performance (gain/loss, MAE, MFE, etc.) on a particular trade idea (a decision to go long/short a particular security at/around a particular time based on a particular setup), which may compromise multiple entries and exits.

Open to ideas!

Since there may be multiple entries, and these entries have not necessarily a fixed number (e.g. you may have a trade scaling in 2 lots, then all-out, or another all-in 3 lots then scaling out, etc.) creating columns for each individual trade would be impractical.

What you would need is a row for each trade entry/exit, as well as some identifier that ties all those entries/exits to a single trade, so that you are able at a later stage or in a separate worksheet to summarize those trades by PnL or other parameters.

Depending on the frequency of your trades, the identifier could be something like a simple counter that increases by 1 for each new trade, or something like a timestamp (e.g. YYYYMMDD-HHMM) so that you're tracking for instance when one trade was first initiated.

You would probably want to consider separate spreadsheets for 'trade headers' and 'trade details' if, for instance, you track MAE/MFE per trade and not per individual leg. The reason I suggest this is that it would not make sense to duplicate the same MAE/MFE data for multiple entry/exit rows when the info is the same.

As I said before, there's many ways to go about it. Excel is one way. MS Access is another option.


I wasn't able to quickly locate the spreadsheet in question from the link you posted. Would you be able to detach it and reattach it here on FIO?

Reply With Quote
The following 2 users say Thank You to xplorer for this post:
 
(login for full post details)
  #6 (permalink)
 swinger 
Raleigh NC/USA
 
Experience: Beginner
Platform: NinjaTrader
Trading: stocks
 
Posts: 32 since Oct 2014
Thanks: 183 given, 15 received


xplorer View Post
Since there may be multiple entries, and these entries have not necessarily a fixed number (e.g. you may have a trade scaling in 2 lots, then all-out, or another all-in 3 lots then scaling out, etc.) creating columns for each individual trade would be impractical.

What you would need is a row for each trade entry/exit, as well as some identifier that ties all those entries/exits to a single trade, so that you are able at a later stage or in a separate worksheet to summarize those trades by PnL or other parameters.

Right, I have a row for each trade entry/exit in a transactions worksheet. A separate column with an identifier is easy enough; e.g., AAPL_1 for the first 'trade' in Apple (constituting multiple entries/exits). AAPL_2 for the second trade in Apple, and so on.


xplorer View Post
Depending on the frequency of your trades, the identifier could be something like a simple counter that increases by 1 for each new trade, or something like a timestamp (e.g. YYYYMMDD-HHMM) so that you're tracking for instance when one trade was first initiated.

Trade frequency is not high, so it's easy enough to use the manual labeling I described above.


xplorer View Post
You would probably want to consider separate spreadsheets for 'trade headers' and 'trade details' if, for instance, you track MAE/MFE per trade and not per individual leg. The reason I suggest this is that it would not make sense to duplicate the same MAE/MFE data for multiple entry/exit rows when the info is the same.

Yeah, here's where it gets tricky. It makes more sense to look at MAE/MFE on an overall trade basis than for each exit constituting the overall trade. And so it would make sense to use a separate worksheet for that. Said worksheet could be set up to pull in data from a pivot table that summarizes the multiple entries/exits constituting a trade. So for example, in the pivot table, I'd have AAPL_1 and that row would show data such as gain/loss, shortest holding period, longest holding period, etc., which the pivot table pulls from the transactions worksheet.


xplorer View Post
As I said before, there's many ways to go about it. Excel is one way. MS Access is another option.

Hadn't considered a database. I'd be wary that I'd be spending too much time on learning and manipulating the database than on gleaning insights that summarizing the individual trade legs would provide.


xplorer View Post
I wasn't able to quickly locate the spreadsheet in question from the link you posted. Would you be able to detach it and reattach it here on FIO?

I downloaded the spreadsheet in Excel format and attached. It's a Google Sheets spreadsheet, so I assume things will break when saved as .xlsx, but can't say for sure as I don't use Excel (I use Google Sheets and Libre Calc). The relevant worksheet is the Transactions worksheet - this is where each individual entry/exit gets recorded. It's a matter of adding columns to this sheet (such as trade identifier discussed above) that would be useful once summarized into a pivot table, and then said pivot table could be used to feed a separate worksheet that captures data like MAE/MFE.

The original Google Sheets spreadsheet can be found here, scroll down to " Dividend Stock Portfolio Tracker with Transaction Page" by Scott K.

Attached Files
Register to download File Type: xlsx Dividend Stock Portfolio Tracker with Transaction Page.xlsx (85.9 KB, 24 views)
Started this thread Reply With Quote
The following user says Thank You to swinger for this post:
 
(login for full post details)
  #7 (permalink)
 xplorer 
Site Moderator
London UK
 
Experience: Beginner
Platform: CQG
Broker: S5
Trading: Futures
 
xplorer's Avatar
 
Posts: 5,491 since Sep 2015
Thanks: 13,963 given, 13,664 received


swinger View Post
Right, I have a row for each trade entry/exit in a transactions worksheet. A separate column with an identifier is easy enough; e.g., AAPL_1 for the first 'trade' in Apple (constituting multiple entries/exits). AAPL_2 for the second trade in Apple, and so on.

If you trade only stocks that's an option. I may suggest to think through the numbering after the stock code in case you want to sort the trades by trade sequence: in the example above you would struggle after trade 9 as spreadsheets sorting alphabetically would start sorting AAPL_1 first, then AAPL_10, then AAPL_11 and so on before moving on to AAPL_2. The workaround would be to sort by date, but again this kind of analysis is best done when one has clear in mind what kind of data manipulation is needed.

Another consideration: because the greater the spreadsheet use the more appetite for data manipulation and analysis, engineering it now in such a way that it is flexible enough for tomorrow (should you have an unplanned need) but still simple enough that it does not take you ages to enter trade data may be a good idea.


Quoting 
Yeah, here's where it gets tricky. It makes more sense to look at MAE/MFE on an overall trade basis than for each exit constituting the overall trade. And so it would make sense to use a separate worksheet for that. Said worksheet could be set up to pull in data from a pivot table that summarizes the multiple entries/exits constituting a trade. So for example, in the pivot table, I'd have AAPL_1 and that row would show data such as gain/loss, shortest holding period, longest holding period, etc., which the pivot table pulls from the transactions worksheet.

Yes, however bear in mind that manipulating Pivot Table data can sometimes be tricky, especially when you add multiple columns. I would recommend looking at PowerPivot which is a free Excel extension but given you don't use Excel it's pointless. Plus there's a learning curve.


Quoting 
Hadn't considered a database. I'd be wary that I'd be spending too much time on learning and manipulating the database than on gleaning insights that summarizing the individual trade legs would provide.

Absolutely. If you do not have at least basic db skills it would probably not be worth it.


Quoting 
I downloaded the spreadsheet in Excel format and attached. It's a Google Sheets spreadsheet, so I assume things will break when saved as .xlsx, but can't say for sure as I don't use Excel (I use Google Sheets and Libre Calc). The relevant worksheet is the Transactions worksheet - this is where each individual entry/exit gets recorded. It's a matter of adding columns to this sheet (such as trade identifier discussed above) that would be useful once summarized into a pivot table, and then said pivot table could be used to feed a separate worksheet that captures data like MAE/MFE.

thanks, I looked at the Transaction sheet. Looks decent enough as starting point.

Not sure if you're aware but there are several spreadsheets dotted around FIO. Those also may offer a few ideas on how to proceed.

Reply With Quote
The following user says Thank You to xplorer for this post:


futures io Trading Community Psychology and Money Management > Trading metrics spreadsheet for multiple entries/exits?


Last Updated on January 29, 2017


Upcoming Webinars and Events
 

NinjaTrader Indicator Challenge!

Ongoing
     



Copyright © 2021 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada), info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts