Snap To Excel - RTD - Sharekhan- Algo trading

[MSN1979]

Hi

Any of the senior members here have used sharekhan Snap to Excel feature and OAlert. I am not from technical background. I need help regarding the same as I am trying to write small code snippets in VBA using Excel ( Developer Feature). Also if there is a thread available please guide me to it.

Also if anyone has information about if sharekhan charges for these features? As they are enabled automatically on my software.


Also any one interested in developing code together would be better. I am reading many articles on internet and I will post updates here on this thread about the things that I learn.

 

[MSN1979]

Ok So I will start my learning Journey. As I am new to this, I feel Working on Excel is easy then programming languages like python etc.

So Lets begin with How to snap Live Data to excel.

Step 1: You should have account with broker that supports snap to excel feature. I am using sharekhan.

Step 2: Select the stocks which you would like to work on in your MarketWatch.

Step 3: Right click and click snap to excel then click new snap. Give your file any name

Step 4: You should get Live streaming data in Excel File

Step5: You will see 3 sheets created by sharekhan

Sheet 1: Streaming_stock_watch: we are not suppose to edit this sheet
Sheet 2: Work Area This is where we can use VBA code or programming functions
Sheet 3 A default sheet for additional Functions

In My next post I will post how to work with various rows columns and cells with some basic useful code snipets

 

[MSN1979]

Ok Here is my first function in VBA

I tried to read a Cell, it took lot of attempts but it turned out to be fairly simple to read the cell

Step 1: Ensure you have a Developer Tab in Excel for VBA

In case it is not present in excel you can simply enable it by Clicking on File -> Options ->Customize Ribbon->Main Tab-> Check Developer this will give you access to code in VBA

Step 2 : Once you have the Developer Tab you are ready to code

So now we go to WorkArea Sheet in Excel and Try to read from a Cell

To read a cell We will Click on Developer Tab ->Insert and then select Command Button

Command Button is like a Genie - But it will only do what you tell him to do

Now draw this command button by selecting your left mouse button and place it anywhere in the empty area of the worksheet

It will ask you to type name for this command button. Type readcell and then click new and you will get a VBA window. You should automatically see

Sub readcell()
END Sub

You should paste rest of code inside it and then click on run button on top. Also you can go to excel sheet and click on Read Cell and you will get a input box asking you to enter which Cell You would like to read.

Sub readcell() // This will activate the readcell button
Dim name As String // name is a variable of type string (You can store alphabets in this). As we studied in math variables values keep on changing or can be kept same

Dim readcell As String

name = InputBox("Enter the Cell Number") //this will display a box on our screen and would prompt us to enter the cell number
Lets say only enter 1 Cell Number example b8 ( So this gets stored in name variable)
readcell = Range(name).Value // Range is a function which will pick value of name and store in read cell variable

MsgBox "The Value of Selected Cell is " + readcell
// This will display value of B8 in a message box on your screen

End Sub // I think this is close of function readcell

Looks simple but lot of hard work to understand programming.

So now we know how to read from a cell and how to display its value. Will write more as I am experimenting.

 

[MSN1979]

Result Of Read Cell

 

[Pradeep Narayan]

I had used this feature for showing real time/ almost real time alerts for specific price conditions. Since you cannot trade from excel, I did not pursue much on this. BTW, there is an inherent data refresh problem with Sharekhan... for example, in the big trade list the scrip prices are updated only if the scrips are in the Marketwatch window. So if you have a excel snap that has 100 scrips only the ones that are in the active marketwatch window will be updated in excel. Lets say you have a snap of all F&O stocks and your marketwatch window has scrips from A to E. In this case a snap of this MW will not update scrips like Tatamotors etc. as they are not on the active window. You will have to scroll up and down completely for couple of times to sync data.

 

[MSN1979]

I see an option in sharekhan that says SNAP to EXCEL with ORDER. I think they have started placing orders from excel. Yes thanx for the other info but at present I only plan to trade 1 scrip using Excel. Even if I am able to backtest I would be more then happy. Currently I am studying DDE but have a hard time how to use OnData function in Excel.

 

[Pradeep Narayan]

Cannot comment on why you need to do VBA.
Most of the work reqd can be done on excel (Sheet 2 in Snap) or create a new worksheet and link to Sheet 2 for data.

 

[MSN1979]

Cannot comment on why you need to do VBA.
Most of the work reqd can be done on excel (Sheet 2 in Snap) or create a new worksheet and link to Sheet 2 for data.

I have created a code that records the value when data is updated in excel sheet every second. It stores the RTD and once the data is stored I would Like to use it for trading purposes.

This code works fine when I enter a Value in Sheet but would not work if worksheet is getting auto refreshed. So someone on internet told me to use OnData but since I am not technical I am kind of stuck.

Private Sub Worksheet_Change(ByVal Target As Range)
''Because this program is in the DDE worksheet, it runs
''each time a value changes
''Do something only if the value changes in cell A1
If Target.Address = "$A$1" Then
''Look at the full list below the Target title
With ThisWorkbook.Names("ListDDE").RefersToRange.CurrentRegion
''Look at the cell at the bottom of the list
With .Offset(.Rows.Count, 0).Resize(1, 1)
''Enter the current time in the cell
.Value = Now
''Enter the new value to the right of the time
.Offset(0, 1).Value = Target.Value

End With
End With
End If
End Sub

 

[Pradeep Narayan]

Try running your code on a separate worksheet and link only one cell to the snap sheet2. That cell will get updated real time, but the sheet will not refresh.

 

[VJAY]

Dear msn,
What is your need to do this coding?is it for auto trading?or anything else?