Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
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 for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
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.
Can you help answer these questions from other members on NexusFi?
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.
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?
The following 2 users say Thank You to xplorer for this 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.
Trade frequency is not high, so it's easy enough to use the manual labeling I described above.
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.
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.
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.
The following user says Thank You to swinger for this post:
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.
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.
Absolutely. If you do not have at least basic db skills it would probably not be worth it.
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.
The following user says Thank You to xplorer for this post: