NexusFi: Find Your Edge


Home Menu

 





Trading metrics spreadsheet for multiple entries/exits?


Discussion in Psychology and Money Management

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




 
Search this Thread

Trading metrics spreadsheet for multiple entries/exits?

  #1 (permalink)
 swinger 
Raleigh NC/USA
 
Experience: Beginner
Platform: NinjaTrader
Trading: stocks
Posts: 33 since Oct 2014
Thanks Given: 196
Thanks Received: 16

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 NexusFi?
Request for MACD with option to use different MAs for fa …
NinjaTrader
My NT8 Volume Profile Split by Asian/Euro/Open
NinjaTrader
ZombieSqueeze
Platforms and Indicators
NexusFi Journal Challenge - April 2024
Feedback and Announcements
 
Best Threads (Most Thanked)
in the last 7 days on NexusFi
Retail Trading As An Industry
58 thanks
Battlestations: Show us your trading desks!
55 thanks
NexusFi site changelog and issues/problem reporting
48 thanks
What percentage per day is possible? [Poll]
31 thanks
GFIs1 1 DAX trade per day journal
29 thanks

  #3 (permalink)
 
xplorer's Avatar
 xplorer 
London UK
Site Moderator
 
Experience: Beginner
Platform: CQG
Broker: S5
Trading: Futures
Frequency: Never
Duration: Never
Posts: 5,926 since Sep 2015
Thanks Given: 15,424
Thanks Received: 15,238



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)
 swinger 
Raleigh NC/USA
 
Experience: Beginner
Platform: NinjaTrader
Trading: stocks
Posts: 33 since Oct 2014
Thanks Given: 196
Thanks Received: 16


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
  #5 (permalink)
 
xplorer's Avatar
 xplorer 
London UK
Site Moderator
 
Experience: Beginner
Platform: CQG
Broker: S5
Trading: Futures
Frequency: Never
Duration: Never
Posts: 5,926 since Sep 2015
Thanks Given: 15,424
Thanks Received: 15,238


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:
  #6 (permalink)
 swinger 
Raleigh NC/USA
 
Experience: Beginner
Platform: NinjaTrader
Trading: stocks
Posts: 33 since Oct 2014
Thanks Given: 196
Thanks Received: 16


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
Elite Membership required to download: Dividend Stock Portfolio Tracker with Transaction Page.xlsx
Started this thread Reply With Quote
The following user says Thank You to swinger for this post:
  #7 (permalink)
 
xplorer's Avatar
 xplorer 
London UK
Site Moderator
 
Experience: Beginner
Platform: CQG
Broker: S5
Trading: Futures
Frequency: Never
Duration: Never
Posts: 5,926 since Sep 2015
Thanks Given: 15,424
Thanks Received: 15,238


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:





Last Updated on January 29, 2017


© 2024 NexusFi™, s.a., All Rights Reserved.
Av Ricardo J. Alfaro, Century Tower, Panama City, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada)
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.
About Us - Contact Us - Site Rules, Acceptable Use, and Terms and Conditions - Privacy Policy - Downloads - Top
no new posts