Trading metrics spreadsheet for multiple entries/exits? - Psychology and Money Management | futures io social day trading
futures io futures trading


Trading metrics spreadsheet for multiple entries/exits?
Updated: Views / Replies:1,006 / 6
Created: by swinger Attachments:1

Welcome to futures io.

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

futures io is the largest futures trading community on the planet, with over 100,000 members. At futures io, our goal has always been and always will be to create a friendly, positive, forward-thinking community where members can openly share and discuss everything the world of trading has to offer. The community is one of the friendliest you will find on any subject, with members going out of their way to help others. Some of the primary differences between futures io and other trading sites revolve around the standards of our community. Those standards include a code of conduct for our members, as well as extremely high standards that govern which partners we do business with, and which products or services we recommend to our members.

At futures io, our focus is on quality education. No hype, gimmicks, or secret sauce. The truth is: trading is hard. To succeed, you need to surround yourself with the right support system, educational content, and trading mentors Ė all of which you can find on futures io, utilizing our social trading environment.

With futures io, you can find honest trading reviews on brokers, trading rooms, indicator packages, trading strategies, and much more. Our trading review process is highly moderated to ensure that only genuine users are allowed, so you donít need to worry about fake reviews.

We are fundamentally different than most other trading sites:
  • We are here to help. Just let us know what you need.
  • We work extremely hard to keep things positive in our community.
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts.
  • We firmly believe in and encourage sharing. The holy grail is within you, we can help you find it.
  • We expect our members to participate and become a part of the community. Help yourself by helping others.

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

Reply
 
Thread Tools Search this Thread
 

Trading metrics spreadsheet for multiple entries/exits?

  #1 (permalink)
Raleigh NC/USA
 
Trading Experience: Beginner
Platform: NinjaTrader
Favorite Futures: stocks
 
Posts: 28 since Oct 2014
Thanks: 108 given, 11 received

Trading metrics spreadsheet for multiple entries/exits?

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.

Reply With Quote
 
  #2 (permalink)
Quick Summary
Quick Summary Post

Quick Summary is created and edited by users like you... Add FAQ's, Links and other Relevant Information by clicking the edit button in the lower right hand corner of this message.

 
  #3 (permalink)
London UK
 
Trading Experience: Beginner
Platform: CQG
Broker/Data: S5
Favorite Futures: Futures
 
xplorer's Avatar
 
Posts: 4,688 since Sep 2015
Thanks: 11,497 given, 10,054 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
 
  #4 (permalink)
Raleigh NC/USA
 
Trading Experience: Beginner
Platform: NinjaTrader
Favorite Futures: stocks
 
Posts: 28 since Oct 2014
Thanks: 108 given, 11 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!

Reply With Quote
 
  #5 (permalink)
London UK
 
Trading Experience: Beginner
Platform: CQG
Broker/Data: S5
Favorite Futures: Futures
 
xplorer's Avatar
 
Posts: 4,688 since Sep 2015
Thanks: 11,497 given, 10,054 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 user says Thank You to xplorer for this post:
 
  #6 (permalink)
Raleigh NC/USA
 
Trading Experience: Beginner
Platform: NinjaTrader
Favorite Futures: stocks
 
Posts: 28 since Oct 2014
Thanks: 108 given, 11 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, 11 views)
Reply With Quote
The following user says Thank You to swinger for this post:
 
  #7 (permalink)
London UK
 
Trading Experience: Beginner
Platform: CQG
Broker/Data: S5
Favorite Futures: Futures
 
xplorer's Avatar
 
Posts: 4,688 since Sep 2015
Thanks: 11,497 given, 10,054 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:

Reply



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

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Upcoming Webinars and Events (4:30PM ET unless noted)
 

futures io is celebrating 10-years w/ over $18,000 in prizes!

Right now
 

Thanks for everything -- $250 in Amazon Gift Cards ready to give away

Right now
 

Journal in July -- over $2,500 in prizes w/NinjaTrader

July
 

How-to Succeed in Trading w/Peter Davies

Jul 9
     

Similar Threads
Thread Thread Starter Forum Replies Last Post
Entries and Exits for Pullbacks/Retracements in Trends? SpencerEng Traders Hideout 6 December 1st, 2015 11:07 AM
Multiple Entries and Exits Hussa TradeStation 7 May 14th, 2015 07:03 AM
How to mark entries and exits on a chart keymoo The Elite Circle 5 April 23rd, 2013 04:05 PM
Arrows to mark trade entries and exits? plethora ThinkOrSwim Programming 1 March 3rd, 2011 09:47 PM


Tags
amp, buy, code, data, database, day trading, dow, how to, information, loss, mae, mfe, pivots, portfolio, scaling, short, signal, spreadsheets, stocks, swing, swing trading, templates, trade entry, trading, values

All times are GMT -4. The time now is 04:32 AM. (this page content is cached, log in for real-time version)

Copyright © 2019 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432 WhatsApp Business, 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