# Connect Zerodha Pi To Excel To Build An Excel Trading System

Few days back I have written How To Create Intraday Trading Excel Sheet From Sharekhan TradeTiger? That post has got very good response from my readers and I have got many request from my Zerodha clients for posting know how on creating simiter excel trading system connecting Zerodha Pi to excel. If you dont have Zerodha Pi, you can apply for a Zerodha account by clicking HERE.

First login to your Zerodha Pi terminal. Once Pi is logged in open the Nifty50 all scrips market watch by clicking File –> Predefined WorkSpace –> NSE –> Nifty 50. It will load all Nifty50 stocks in your marketwatch.

My Nifty50 MW (my given name for this marketwatch) is open. Now the marketwatch needs to be sorted by symbol name. So please click on Trading Symbol column header to sort it alphabatically.

Now right click on the market watch and click LINK TO EXCEL (Ctrl+E). This will connect Zerodha Pi to excel and save the market watch data in an excel sheet to the location \Zerodha\Pi\LinkExcel\Nifty50 MW.xlsx.

If you have already a saved excel in that location, for second time use or recurring use, it will ask you to replace the excel file.

Now the excel file will automatically open. It will contain only 1 sheet called Nifty50 MW (same name as marketwatch). In case the market is open live price will start changing in the excel.

Now we will create another sheet and write formulas there. Lets work on the same formula Open High Low Same Trading Strategy For Indian Stock Market. To build our excel trading system lets create 2 columns in sheet2 called Open=Low and Open=High.

The formula for Open=Low column 2nd row is:

AND

The formula for Open=High column 2nd row is:
=IF(‘Nifty50 MW’!H2=’Nifty50 MW’!I2,”SELL”,””)

Now select the row A2 and hold the extreme right bottom corner of the cell and drag below through entire column. It will copy the formula in the rows below. Repeat the step for B2 column too. Check the image below:

So in the excel trading system, now we get BUY or SELL scrips. Today (2nd March 2017), I have got AMBUJACEM, COALINDIA AND ULTRACEMCO as BUY and got DRREDDY, GRASIM, INDUSINDBK, NTPC and POWERGRID as sell.

Setting target and stop loss in the Excel Trading System:
I have added a 2.5% target and a setup a stop loss 2 ticks below day’s low for BUY and 2 ticks above day’s high for days low. I will round the targets and stop loss to 2 decimal places by formating the excel cells as shown below. Finally I have decorated the sheet a bit and renamed the sheet2 as trade.

So we have completed creating a excel day trading spreadsheet by connectiong Zerodha Pi to excel. If you are unable to connect Zerodha Pi to excel yourself, you can download my excel sheet from below.

l levels instantly. Check this youtube video for the whole process of data import:

You can use this excel sheet for creating creating other buy / sell logics too. Your comments below this post can take this discussion to further heights.

## 36 thoughts on “Connect Zerodha Pi To Excel To Build An Excel Trading System”

1. Avinash J.R. says:

Thanks a million for the email regarding excel sheet i’ll try it out.I request you to do some research regarding Darvas box which is there in Pi chart settings.

2. Kaushal Mehta says:

Hello Indrajit,
How can we keep the Trading calls generated from the open=low/open=high system constant till the end of the day in excel sheet?

1. Kaushal, they will remain constant unless and until the high / low gets breached. To keep it same throughout the day you need to simply close the market watch OR Pi. This will stop Link To Excel command.

3. Rajam Ananthakrishnan says:

Hi Indrajit, can you help in creating a strategy based on Supertrend for scanner on Pi?

4. Abhijit says:

Hello, Good Work –

5. Anup Gogoi says:

Sir can you please make same excel fo BANKNIFTY also.

1. Anup Gogoi says:

Thanks

6. Chandra Mohan Baloni says:

can we place order from excel itself

1. Chandra Mohan yes you can place order from excel itself. For that you need to write your buy sell code in excel and connect excel to Pi with Pi Trading Bridge.

7. Chandra Mohan says:

1. Chandra I am sorry, my knowledge is limited till manual trading and code writing for manual trading. For automation you may visit this page: Algo Trading. Saurabh the master trainer can train you on automation.

8. Jyotirmay Patel says:

Brilliant……Excellent tricks in details. Thank you.

9. Soumendra Mishra says:

Sir I am using Excel 2016 32bit & my OS is Windows 10 but I am not able connect Pi to Excel only I am getting (NA)

1. Soumendra, make sure you have named your excel file exactly the same name as MarketWatch.

10. Soumendra Mishra says:

Yes sir one is Default and other is Nifty 50 but when I am connecting the excel file opening automatically with the marketwatch name and the content is NA

11. Soumendra Mishra says:

Sir for amibroker (above mentioned link) the excel file is generated in exported folder this is fine but from LinkExcel file the price is not coming all the cells are marked .NA. Is there any extra configuration required ?

12. Anil says:

Sir,
Is it possible to export data from excel sheet back to Zerodha PI ?

13. shweta says:

Hi Indrajit,

Thanks for such a detailed description.

I would like to get the buy/sell signals created in Zerodha Pi Expert Advisor into MsExcel.

Could you please let me know how to do it.

Thanks,
Shweta

14. Craig Albuquerque says:

can we save hourly Data in separate Sheet ?…from input, live intraday Data (From Pi).

Hello sir,

Thank you for this wonderful post but i have a small problem which time frames high or low you prefer i.e. we came to know if open and high of first candle is same the we should sell and vice versa for buy but problem is which time frames candle we have to choose.

And one more information is i want to export data from pi to excel but i want only 1st 15min candles data how to restrict this sir?

1. I feel your excel file is not having the same name of your market watch.

17. Dr. Anurag Singhal says:

Wonderful Indrajit.
Exactly what I was looking for.

18. Kehal Pola says:

Can you make a excel sheet that scan stocks that going up with increase volume and going down with increase volume ?

19. Excel file is showing NA values. MArketwatch name is same although. Pl guide

1. Make sure you have saved the excel to your /Zerodha/Pi/LinkExcel folder.