With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Sunday, August 12, 2012
yogi_Compute Stats In A Trade Log Of Long And Short Trades
I am trying to add an additional IF function to columns H, I, and J. Here is a breakdown on what each is currently set up to do:
H: =IF(C3="Long",(D3*G3)-(D3*E3),(D3*E3)-(D3*G3))-14 This determines whether the stock is a Long or Short in order to calculate the profit between the entry and exit point. It also deducts 14 for commission at the end.
I: =H3/(D3*E3) This calculates the percentage gain relative to the trade size.
J: =F3-A3+1 This calculates how many days the trades were open.
Also, please notice the tables on the right side of the spreadsheet, which analyze these results based on different criteria. I have currently eliminated the formulas from certain fields because since the trades are still open, and there is no exit price, it would generate wrong results. For example, if I were to carry the column H and I formula down to row 4, it would show a complete loss of $3014 and 100%. This would in turn skew the right tables' overall analysis.
So my question is this, what formula would I need to put in to these columns so that still open trades will generate a 0 or N/A or something that would not affect my analysis in the right tables, AND trades that have completed will calculate the results as I have them now. I have tried everything but I can't figure it out. Please help! Any help would be greatly appreciated.