I created a very simple Excel Spreadsheet that contains, ticker, entry, current price, stop, target, target%, stop%, risk/reward ratio, $loss,$gain,initial investment, as well as current gain/loss. These formulas that I have made are all assuming I would be playing on the long side. If I were to have a short position, the current formula gets all messed up. I was wondering if anyone can give me some simple tips to calculate for both short/long position or if there is already a similar spreadsheet made. Thanks!
You might want to take a look at ExcelTradingModels. They have both free and paid resources available that might help you.
I presume you have a column which details how many contracts/ no. of shares you have and that feeds the other columns like profit column? If so, for short positions just put '-' before the number.
Here is a sample of the spreadsheet I am using. https://docs.google.com/spreadsheets/d/1u-ou6-1yGTARMgAtaZargWxqihSX70vDHHCRiAxicP4/edit?usp=sharing Visaria the negative # did help somewhat in terms of the $ risk, $ initial investment, $gain/loss, and reward$. However the target % , stop%, and trailing stop% are still lop sided.
Yeah, you need to change the forumulae in those columns by way of an IF function, so it inputs a different formula for short positions. Very simple. For example in cell G4, you would put "=IF(q4<0, -(f4/c4)-1,(f4/c4-1)). All this is saying is if the value in Q4 is negative i.e short, then use this formula, otherwise use the normal formula.