Free data from NSENOW to AmiBroker via Excel

[DataRam]

Hi Josh1,

I am not a technical person so bit confused about the Backfill Data feature. I have still not used the Backfill data utility for Nest Plus but will work on it this weekend.
Actually I want to ask that will the Real Time Data gets integrated to the Backfill data in Amibroker or a separate database is needed for the backfill data ? Your help is genuinely appreciated.

Thanks.

 

[josh1]

Hi Josh1,

I am not a technical person so bit confused about the Backfill Data feature. I have still not used the Backfill data utility for Nest Plus but will work on it this weekend.
Actually I want to ask that will the Real Time Data gets integrated to the Backfill data in Amibroker or a separate database is needed for the backfill data ? Your help is genuinely appreciated.

Thanks.

Real Time data gets integrated with backfill data. Read the last para of Readme file. I have improved the NOWBackfil.xlsm utility. Now you will get choice to indicate your database path in its "Masters" worksheet. I have also revised the readme file with explanation of all the code. Please download the file from here.
http://www.4shared.com/zip/uDvirNMF/NSE-NOW-RT2.html


http://www.4shared.com/zip/uDvirNMF/NSE-NOW-RT2.html

By the way, are you successful in getting the realtime feed ?

 

[DataRam]

Real Time data gets integrated with backfill data. Read the last para of Readme file. I have improved the NOWBackfil.xlsm utility. Now you will get choice to indicate your database path in its "Masters" worksheet. I have also revised the readme file with explanation of all the code. Please download the file from here.
http://www.4shared.com/zip/uDvirNMF/NSE-NOW-RT2.html


http://www.4shared.com/zip/uDvirNMF/NSE-NOW-RT2.html

By the way, are you successful in getting the realtime feed ?

Thanks for your reply. I worked upon getting Real Time Data from NOW but was unable to do so . I am not at all a techie person so might be doing mistakes . I have downloaded the latest file you have provided through this link. I hope to get success.

Regards.

 

[josh1]

Thanks for your reply. I worked upon getting Real Time Data from NOW but was unable to do so . I am not at all a techie person so might be doing mistakes . I have downloaded the latest file you have provided through this link. I hope to get success.

Regards.

Do it step by step. Get the data feed in Excel first and thereafter in AmiBroker. Let me know specific problem.

 

[josh1]

Now I am going to explain the operating instructions.

1. Minimum requirement is Excel 2007 and Windows. Tested on Windows XP and Windows 2003 x64 Server.

2. Extract the zip file.There are five files in it 1.NSE-NOW-RT2.xlsm, NSENOW2.format, NowBackfil.xlsm, Nest.format and Readme file.

3. NSE-NOW-RT2.xlsm and NOWBackfill.xlsm and can be kept anywhere. However, I recommend to keep it in C:\Program Files\NOW or C:\Program Files (x86)\NOW depending upon your version of windows, wherever your "NOW" or Nest Trader or wherever your Trading Software application resides. Else create a separate folder and keep them there.

4. Move NSENOW2.format and Nest.format file to format folder under - Your\AmiBroker\Path. c:\program files\amibroker\format in most cases.

5. You can open the NSE-NOW-RT2.xlsm file from Excel.

6. Open Excel. Click on the Office icon on top left and then click on Excel Options- Trust Center - Trusted Locations - Add New Location and give the path that you chose in Step 3. If you kept the .xlsm files in separate folder, give the location of your Trading/Data Feed application also. This way, excel will open the .xlsm file with macros enabled. Otherwise, you will have to enable macros every time.

 

[josh1]

7. See the figure, Date column is to the leftmost. This will allow us to add new scrips. Remember to copy the formula from any of the cells in that column to the cell preceding new scrip.

8. Do not change the order of columns. For ease of linking, keep the order of columns same in NOW or NEST TRADER.

9. You can add/delete columns if you wish. You will have to edit the NSENOW2.format file. However first four columns are mandatary.

10. Check the database path in cell C1. If your database is at a different location, enter that in cell C1. In my case, it is on H drive. I am assuming that databse is already created. If not create the database from AmiBroker.

11. Look at the bottom left of the worksheet. You will see three tabs. Now, Start and Stop. Click on “Start”. Excel will detect if NOW is running. If not, it will start NOW and ask you to login and terminate. Those who are capturing data from some other software, will have to disable this functionality.

 

[josh1]

If NOW is not running, you will see "N/A" in all the cells. Hence it is better to start NOW before starting NSE-NOW-RT2. If now is already open, check that the columns Sybol, Last Trade Time, Last Traded Price, and Last Traded Qty are present in Market Watch. If not, RightClick on top of Market watch window. Click on Reposition columns and add these columns. Keep the columns in the same order as in Excel and remove other columns.

Click and select any of the scrips. RightClick again, Click on Link to Excel- Selected items. Then right click on empty cell in column and paste. You will see rates data in excel and rates changing.

 

[josh1]

13. Check wheteher "Developer" tab is open else enable it thrugh Excel Options - Popular - Show Developer tab in Ribon.

14. The code is small, open and you can change it to suit your purpose. I have given suitable remarks everywhere.

15. Now I am going to explain the code. Click on Visual Basic in the Deveveloper Tab of Excel. A window like this will open. If the right side is blank, click inside VBA Project part on the left side. Double Click on Sheet3 (Start) and you will see the code on right side. Comments inside code are given in Green

16. Here is the code-
Private Sub Worksheet_Activate()
Module1.StartTimer 'Calls StartTimer Subroutine on activation/Click of Sheet3 i.e. Start
End Sub

17. DoubleClick on Sheet2 (Stop). Following code will be displayed-
Private Sub Worksheet_Activate()
Module1.Stop_Timer 'Calls StopTimer subroutine on activation/Click of Sheet2 i.e. Stop
End Sub

18. DoubleClick on Module1. All other code, subroutines will be displayed.

 

[josh1]

19. This part declares all variables used in the workbook.
Dim TimerActive As Boolean
Public FileName As String
Public AB As Object 'Declare AmiBroker as a public variable.
Public ABPath As String 'Declare database path as a public variable
Public DBPath As String 'Variable to collect User'sDatabase path


20. The StartTimer Subroutine is called by clicking on Start Worksheet. It calls the IsNOWRunning subroutine to check whether NOW is running. If NOW is not running it will close the application. If it is running then it will start AmiBroker, activate the Timer and thereafter execute the Timer Subroutine.

Sub StartTimer()
IsNOWRunning 'Check whether NOW is running. If not close down.
Those taking data feed from some other software should delete or comment out the above line.
InitialiseAB 'Start AmiBroker
TimerActive = True
Timer ' Calls Timer Subroutine
End Sub

21. The Timer subroutine is currently set to run every three seconds if Timer is active. Explanations are in green.
Private Sub Timer()
If TimerActive = True Then
MakeCSV 'Calls Subroutine for generating csv file
CallAmiBroker 'Calls AmiBroker for importing file
'The code below runs the Timer subroutine every 3 seconds
Application.OnTime Now() + TimeValue("00:00:03"), "Timer"

End If
End Sub

 

[josh1]

22. The MakeCSV subroutine generates MYcsv.csv file in C:\ i.e. Root of C Drive.
Sub MakeCSV()

Dim fs As Object, a As Object, i As Integer, s As String, t As String, CellValue As String
'Create a file object for writing
Set fs = CreateObject("Scripting.FileSystemObject")
'Set a as C:\Mycsv.csv, True means overwrite on old file. You can change the location and name of csv file by editing below line.
Set a = fs.CreateTextFile("c:\MyCSV.csv", True)
'Select Cell A1 in the sheet containing quotations i.e. NOW
Application.Goto Workbooks("NSE-NOW-RT2.xlsm").Sheets("Now").Range("A1:A1")
'Iterate through all cells/columns from Row 3 to Row 65536 in which quotations exist and write quotations in the csv file.
For r = 3 To Range("A65536").End(xlUp).Row
s = ""
c = 1
While Not IsEmpty(Cells(r, c))
If Cells(r, c).Value = "0" Then
CellValue = ""
Else
CellValue = Cells(r, c).Value '
End If
s = s & CellValue & "," 'Add contents of current cell to string 's' and a comma
c = c + 1
Wend
a.writeline s 'write contents of String S to the csv file.
Next r 'go to next row

End Sub

23. The InitialiseAB subroutine starts AmiBroker.
Sub InitialiseAB()
On Error Resume Next
Set AB = GetObject(, "Broker.Application")
If AB Is Nothing Then ' True if not running
'Uncomment the line below if AmiBroker is not starting automatically
'ActiveWorkbook.FollowHyperlink "C:\Amibroker\Broker.exe", NewWindow:=True
Set AB = CreateObject("Broker.Application")
End If
AB.Visible = True
'Get the path of database loaded by AmiBroker
ABPath = AB.DatabasePath
'Get the database path from C1 cell in the Sheet NOW.
DBPath = Workbooks("NSE-Now-RT2.xlsm").Sheets("Now").Cells(1, 3).Value
If ABPath <> DBPath Then
AB.LoadDatabase (DBPath)
End If
'Edit and uncomment the lines below if you want to start a specific scrip or layout.
'AB.ActiveDocument ("NIFTY")
'AB.LoadLayout ("Realtime")

End Sub

24. The CallAmiBroker subroutine calls AmiBroker to import quotations from text file named "C:\MyCSV.csv" (we created in subroutine MakeCSV) as per the order of columns stated in the NSENOW2.format file. AmiBroker looks at the $FORMAT line in NSENOW2.format file which at present is like this-
$FORMAT Date_MDY, Ticker, Time, Close, Volume, Appendticker, Appendticker

Sub CallAmiBroker()
FileName = "C:\MyCSV.csv"
Call AB.Import(0, FileName, "NSENOW2.format")
'This line tells AmiBroker to refresh all charts
Call AB.RefreshAll
End Sub