OmniVest User Forums
User-Created Portfolios
Spreadsheet to plot just the closed trades
15 replies, 1711 viewings
|
OmniVest User Forums
User-Created Portfolios
Spreadsheet to plot just the closed trades
|
Last Activity 7/22/2015 8:11 AM 15 replies, 1711 viewings |
|
|
|
| Printer friendly version |
Mark Holstius Elite    Posts: 744 Joined: 10/11/2012 Location: Sleepy Hollow, IL  User Profile |
There are excellent reasons for the way it’s presented in OV, with the chart showing “mark to market” equity and using that for the MDD figures and ending equity – but I feel comfortable with waiting for the trades to close and the attached spreadsheet is an easy way to obtain the data I want to see and use. Here are a couple of examples of the difference in the 2 charts (Closed Trades Equity on top and OV's chart on the bottom); You may not want to look at the data this way… but if you do, feel free to use the spreadsheet for your own testing and observation. (and please let me know if you find any errors or have suggestions...) The SS will handle a portfolio with 50,000 trades (200 trades / month for 20 years). Simply paste the OV historical data into the SS, sort the pasted data by Exit Date, and choose the date range you want to plot. (You can also adjust the starting balance and commissions). The necessary data is extracted and computations are done to the right in the SS (off screen). A simple step-by-step set of directions is attached and included on the “Notes” tab of the SS (“Closed_Trade_Equity_Instructions”). Mark [Edited by Mark Holstius on 10/23/2013 1:57 PM]  Attached file : Example01.png (421KB - 625 downloads) Attached file : Example02.png (1068KB - 637 downloads) Attached file : Chart Of Closed Trades Equity.xlsm (6943KB - 0 downloads) Attached file : Closed_Trade_Equity_Instructions.png (2456KB - 264 downloads) | ||
| Mark Holstius Elite Posts: 744 Joined: 10/11/2012 Location: Sleepy Hollow, IL User Profile |
So - here's a link to the spreadsheet in Dropbox if you want to download it: https://www.dropbox.com/s/ll0msprnrciw8wk/Chart_Of_Closed_Trades_Equity.xlsm (Be sure to enable Macros when you open it) Mark | ||
| jim etter New User Posts: 3 Joined: 3/7/2013 Location: Blythewood, SC User Profile |
Jim | ||
| Steve Mayo Legend Posts: 414 Joined: 10/11/2012 Location: Austin, TX User Profile |
Your comment about the late drop in the OV graph got me to thinking. The only way to truly compare apples to apples would be to, in your closed-trades graph, follow the open trades (and not take any new ones) after the end-date of the reporting period. In other words, to see if the paper losses really do turn into eventual gains on that set of opening trades. Not sure its worth the effort to really program that but it makes for an interesting academic discussion. :-) Steve | ||
| Randy McBeath Member Posts: 13 Joined: 10/11/2012 Location: Mountain View, CA User Profile |
Thanks for sharing this great spreadsheet. Downloaded perfectly from DropBox. Randy | ||
| Mark Holstius Elite Posts: 744 Joined: 10/11/2012 Location: Sleepy Hollow, IL User Profile |
You definitely get different information. Take a look at these charts of the same portfolio plotted in OV (MtM) vs using the OV historical data in the spreadsheet to plot equity as the trades are closed... Mark [Edited by Mark Holstius on 10/23/2013 4:05 PM] Attached file : Chart_Personality.png (631KB - 577 downloads) | ||
| John W Elite Posts: 654 Joined: 10/11/2012 Location: Sydney, NSW, Australia User Profile |
You are very generous in helping others and have terrific continual positive input to all the Forums. WELL DONE AND THANK YOU!! John | ||
| BrianD Legend Posts: 302 Joined: 2/23/2013 Location: Grand Rapids, MI User Profile |
Hopefully, in the near future, real position closing data will be presented in a concise manner by OV so we can do that analysis too. Not an easy report to create though, especially with high TPM profiles. | ||
| Steve Mayo Legend Posts: 414 Joined: 10/11/2012 Location: Austin, TX User Profile |
[Edited by Steve Mayo on 10/23/2013 9:20 PM] | ||
| Jim Dean Elite  Posts: 1059 Joined: 10/11/2012 Location: L'ville, GA User Profile |
Re the mark to market data - if done really thoroughly it would be a LOT of data. Closing price for every in-trade symbol of every active strategy for every day of every trade for 13+ years - a big table! I'd think that the combined sum of all active trades for each day would suffice - that's what the equity graph is built from. Or would it be better to have it by portfolio? [Edited by Jim Dean on 10/24/2013 7:11 AM] | ||
| Steve Mayo Legend Posts: 414 Joined: 10/11/2012 Location: Austin, TX User Profile |
| ||
| Mark Holstius Elite Posts: 744 Joined: 10/11/2012 Location: Sleepy Hollow, IL User Profile |
Like you said, the MtM data must be there to be able to draw those charts so quickly - and it would be EXTREMELY useful for constructing new portfolios! Is there any hope for a download capability in the near future Barry, and could it -please- include the MtM data? Thanks, Mark | ||
| gbarber Veteran Posts: 282 Joined: 12/30/2012 Location: Pearland, TX User Profile |
Thank you very much for this work. As stated in previous posts, I have been a fan of following the closed trades versus mark to market. The closed trades are what OV will actually do for you. The mark to market is hypothetical what might happen. When comparing strategies, I want to see what OV actually does. Mark to market is not relevant since it doesn't actually happen. it is a possible false threat or a possible false exuberance. It's not real until you close the trade. | ||
| gbarber Veteran Posts: 282 Joined: 12/30/2012 Location: Pearland, TX User Profile |
| ||
| George Member Posts: 24 Joined: 9/29/2013 Location: Independence, Minnesota User Profile |
| ||
| Jon Member Posts: 12 Joined: 8/22/2013 Location: TEMPE, AZ User Profile |
|
|
|
|
|
Administrator
Forum Moderator |
Registered User
Unregistered User |
 E-Mail this thread to a friend |
Toggle e-mail notification |
