Derivatives are a great tool for all varieties of market participants. It has now become a popular trading platform for people with diverse need and requirements. Whether you are a hedger or a speculator or an arbitrageur, you need to get into little complex strategies, either to find the perfection in a synthetic hedge or to fine tune your speculative transaction as per the need in volatile markets. With increasing efficiency in the markets, arbitrageurs also get into tricky ones to find the gaps.
Use of excel brings a lot of respite for all such people. Now how to use that….let us learn this in a simple way.
In your trading window, you must have created a market watch or else create one with columns like scrip name, expiry date, option type, strike price, buy price, buy quantity, sell price, sell quantity, last traded price, volume, open interest, etc. You need to open this market watch and right click on it. Within the menu opened, select the option ‘Open in excel’ and your market watch will open in excel. Not only it will open in excel but also the rates will start changing as per changes in the market.
In excel, you may take these rates and use “fx” command to create formulas. Here you may create a formula, for example, buying a difference between two future price.
In order to buy this difference, we will sell near month contract and buy a mid month contract. We can see the result of this formula getting updated with changes in live market by following these steps.
1. Create a new worksheet
2. Press F2 on a cell where you wish to display result or difference value.
3. Type ‘=’
4. Select a cell with selling price of mid month contract
5. Type ‘-‘
6. Select a cell with buying price of near month contract
7. Press enter
You will see a difference of the two contract getting displayed. We can do the same taking last traded price also however that will not reflect the current situation i.e. exact bid/ask quotes.
In a similar manner, you can make any number of strategies for display just like your market watch or radar screen. I have seen few people watching options in a tabular form with strike prices in rows and type of options and expiry month in columns. For people, who likes to trade in differences or ratio of two related scrips, it becomes a ready reckoner of various ration without having to calculate each of them.
In case where a trader wishes to act upon with specific price movement or a price level, he may use ‘Conditional formatting’. In this a cell value may be specified above which the background of the cell may turn green thus highlighting the opportunity. One can also apply conditional formatting, say with difference below one to be reflected with Red base and when difference value crosses 4, the cell to be highlighted with green base.
Use of excel not only eases the building of complex strategies but also make an ease on tracking them up. Thus it becoming very popular among the entire trading community.
Happy trading!!!
