Done.
It's working Perfect.:thumb:
Here is updated code.
----------
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
Public Yahoo As String 'String to hold whetheryahoo data required
Public NSENOW As String 'String to hold whetheryahoo data required
Public MyBook As Workbook
Public Secs As Date
Public IST As String
Public Vol As Variant 'Holds last volume traded today
Sub StartTimer()
'IsNOWRunning 'Check whether NOW is running. If not close down.
Set MyBook = Workbooks("RT31.xlsm")
Secs = TimeValue("00:00:03")
' Google = MyBook.Sheets("Now").Cells(4, 2).Value
Vol = Range("D
")
InitialiseAB 'Start AmiBroker
TimerActive = True
Timer ' Calls Timer Subroutine
End Sub
Public Sub Stop_Timer()
TimerActive = False
MsgBox ("Realtime feed stopped")
AB.SaveDatabase
Set AB = Nothing 'Close the active instance of AmiBroker
End Sub
Private Sub Timer()
NSENOW = MyBook.Sheets("Now").Cells(2, 2).Value
Yahoo = MyBook.Sheets("Now").Cells(3, 2).Value
If TimerActive = True Then
If Yahoo = "Yes" Then
GetData
End If
MakeCSV 'Calls Subroutine for generating csv file
Shell ("C:\\RTDATA\asc2ms -f C:\\RT\MyCSVMS.txt -r r -o C:\\RTDATA\ms")
Application.OnTime NOW() + TimeValue("00:00:03"), "Timer" 'This code runs the Timer subroutine every 3 seconds
End If
End Sub
Sub CallAmiBroker()
Call AB.Import(0, FileName, "RTG3.format")
Call AB.RefreshAll
End Sub
Sub IsNOWRunning()
strComputer = "."
Set objWMI = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set proc = objWMI.ExecQuery _
("Select * from Win32_Process Where Name = 'Now.exe'")
If proc.Count = 0 Then
MsgBox ("Now.exe is not running. I am starting it. Please log in before you continue." _
& "Log into Nestplus* also if you want backfil. After that, Start this application again")
Shell ("C:\Program Files (x86)\NOW\NowLauncher.exe")
Application.DisplayAlerts = False
ActiveWorkbook.Close
End If
End Sub
Sub InitialiseAB()
On Error Resume Next
Set AB = GetObject(, "Broker.Application")
If AB Is Nothing Then ' True if not running
Set AB = CreateObject("Broker.Application")
End If
AB.Visible = True
ABPath = AB.DatabasePath
DBPath = MyBook.Sheets("Now").Cells(1, 2).Value
If ABPath <> DBPath Then
AB.LoadDatabase (DBPath)
End If
AB.LoadLayout ("Realtime")
AB.Window.LoadTemplate ("NowRT.Chart")
End Sub
Sub MakeCSV()
Dim fs As Object, a As Object, y As Object, C As Integer, i As Integer, r As Integer, S As String, t As Variant, CellValue As String
'Create a file object for writing
Set fs = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
MkDir ("C:\RT") 'This will create a folder RT in C Drive.
FileName = "C:\RT\MyCSVMS.txt" 'This file is used to write quotes
Set a = fs.CreateTextFile(FileName, True) 'Here we create the file MyCSV.csv
If NSENOW = "Yes" Then
a.writeline "TICKER,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOLUME,OPEN INTEREST"
MyBook.Sheets("Now").Select 'Selects sheet containing quotations
For r = 7 To Range("A65536").End(xlUp).Row
S = ""
C = 1
While Not IsEmpty(Cells(r, C))
If C = 1 Then
CellValue = Cells(r, C).Value & ",I," & Format$(Date, "yyyymmdd") ' ' (Insert comma and I after ticker name)
ElseIf C = 3 Then 'Insert Last Traded Price 4 times for O,H,L,C
CellValue = Cells(r, C).Value & "," & Cells(r, C).Value & "," & Cells(r, C).Value & "," & Cells(r, C).Value
ElseIf C = 4 Then
CellValue = Cells(r, C).Value - Vol(r, 1)
Vol(r, 1) = Cells(r, C).Value
' Debug.Print Cells(r, 1).Value & " - " & CellValue & " - " & Vol(r, 1)
Else
CellValue = Cells(r, C).Value '
End If
' CellValue = Cells(r, C).Value '
S = S & CellValue & "," 'Add contents of current cell to string 's' and a comma
C = C + 1
Wend
a.writeline S 'write line
Next r
End If
----------
Here is output file
----------
TICKER,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOLUME,OPEN INTEREST
SILVERM12AUGFUT,I,20120715,14:00:00,52819,52819,52819,52819,0,287345,
GOLD12AUGFUT,I,20120715,13:59:59,29272,29272,29272,29272,0,15218,
SILVERMIC12AUGFUT,I,20120715,14:00:00,52831,52831,52831,52831,0,150826,
COPPER12AUGFUT,I,20120715,14:00:00,426.15,426.15,426.15,426.15,0,29564,
CRUDEOIL12AUGFUT,I,20120715,13:59:57,4833,4833,4833,4833,0,609500,
CRUDEOIL12JULFUT,I,20120715,13:59:58,4790,4790,4790,4790,0,2157200,
ZINCMINI12JULFUT,I,20120715,13:59:48,103.5,103.5,103.5,103.5,0,7299,
ZINC12JULFUT,I,20120715,13:59:57,103.45,103.45,103.45,103.45,0,18340,
NATURALGAS12JULFUT,I,20120715,13:59:58,158.7,158.7,158.7,158.7,0,11212500,
GOLDGUINEA12JULFUT,I,20120715,13:59:58,23483,23483,23483,23483,0,243056,
----
Which is working good.
1. I think we need to run shell for as2ms in background as it refreshes complete windows screen. any idea?
2. we need to remove Amibroker related code to release it for production.
---------
I will test this in market hours tomorrow and let you know.
It's working Perfect.:thumb:
Here is updated code.
----------
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
Public Yahoo As String 'String to hold whetheryahoo data required
Public NSENOW As String 'String to hold whetheryahoo data required
Public MyBook As Workbook
Public Secs As Date
Public IST As String
Public Vol As Variant 'Holds last volume traded today
Sub StartTimer()
'IsNOWRunning 'Check whether NOW is running. If not close down.
Set MyBook = Workbooks("RT31.xlsm")
Secs = TimeValue("00:00:03")
' Google = MyBook.Sheets("Now").Cells(4, 2).Value
Vol = Range("D
")InitialiseAB 'Start AmiBroker
TimerActive = True
Timer ' Calls Timer Subroutine
End Sub
Public Sub Stop_Timer()
TimerActive = False
MsgBox ("Realtime feed stopped")
AB.SaveDatabase
Set AB = Nothing 'Close the active instance of AmiBroker
End Sub
Private Sub Timer()
NSENOW = MyBook.Sheets("Now").Cells(2, 2).Value
Yahoo = MyBook.Sheets("Now").Cells(3, 2).Value
If TimerActive = True Then
If Yahoo = "Yes" Then
GetData
End If
MakeCSV 'Calls Subroutine for generating csv file
Shell ("C:\\RTDATA\asc2ms -f C:\\RT\MyCSVMS.txt -r r -o C:\\RTDATA\ms")
Application.OnTime NOW() + TimeValue("00:00:03"), "Timer" 'This code runs the Timer subroutine every 3 seconds
End If
End Sub
Sub CallAmiBroker()
Call AB.Import(0, FileName, "RTG3.format")
Call AB.RefreshAll
End Sub
Sub IsNOWRunning()
strComputer = "."
Set objWMI = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set proc = objWMI.ExecQuery _
("Select * from Win32_Process Where Name = 'Now.exe'")
If proc.Count = 0 Then
MsgBox ("Now.exe is not running. I am starting it. Please log in before you continue." _
& "Log into Nestplus* also if you want backfil. After that, Start this application again")
Shell ("C:\Program Files (x86)\NOW\NowLauncher.exe")
Application.DisplayAlerts = False
ActiveWorkbook.Close
End If
End Sub
Sub InitialiseAB()
On Error Resume Next
Set AB = GetObject(, "Broker.Application")
If AB Is Nothing Then ' True if not running
Set AB = CreateObject("Broker.Application")
End If
AB.Visible = True
ABPath = AB.DatabasePath
DBPath = MyBook.Sheets("Now").Cells(1, 2).Value
If ABPath <> DBPath Then
AB.LoadDatabase (DBPath)
End If
AB.LoadLayout ("Realtime")
AB.Window.LoadTemplate ("NowRT.Chart")
End Sub
Sub MakeCSV()
Dim fs As Object, a As Object, y As Object, C As Integer, i As Integer, r As Integer, S As String, t As Variant, CellValue As String
'Create a file object for writing
Set fs = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
MkDir ("C:\RT") 'This will create a folder RT in C Drive.
FileName = "C:\RT\MyCSVMS.txt" 'This file is used to write quotes
Set a = fs.CreateTextFile(FileName, True) 'Here we create the file MyCSV.csv
If NSENOW = "Yes" Then
a.writeline "TICKER,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOLUME,OPEN INTEREST"
MyBook.Sheets("Now").Select 'Selects sheet containing quotations
For r = 7 To Range("A65536").End(xlUp).Row
S = ""
C = 1
While Not IsEmpty(Cells(r, C))
If C = 1 Then
CellValue = Cells(r, C).Value & ",I," & Format$(Date, "yyyymmdd") ' ' (Insert comma and I after ticker name)
ElseIf C = 3 Then 'Insert Last Traded Price 4 times for O,H,L,C
CellValue = Cells(r, C).Value & "," & Cells(r, C).Value & "," & Cells(r, C).Value & "," & Cells(r, C).Value
ElseIf C = 4 Then
CellValue = Cells(r, C).Value - Vol(r, 1)
Vol(r, 1) = Cells(r, C).Value
' Debug.Print Cells(r, 1).Value & " - " & CellValue & " - " & Vol(r, 1)
Else
CellValue = Cells(r, C).Value '
End If
' CellValue = Cells(r, C).Value '
S = S & CellValue & "," 'Add contents of current cell to string 's' and a comma
C = C + 1
Wend
a.writeline S 'write line
Next r
End If
----------
Here is output file
----------
TICKER,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOLUME,OPEN INTEREST
SILVERM12AUGFUT,I,20120715,14:00:00,52819,52819,52819,52819,0,287345,
GOLD12AUGFUT,I,20120715,13:59:59,29272,29272,29272,29272,0,15218,
SILVERMIC12AUGFUT,I,20120715,14:00:00,52831,52831,52831,52831,0,150826,
COPPER12AUGFUT,I,20120715,14:00:00,426.15,426.15,426.15,426.15,0,29564,
CRUDEOIL12AUGFUT,I,20120715,13:59:57,4833,4833,4833,4833,0,609500,
CRUDEOIL12JULFUT,I,20120715,13:59:58,4790,4790,4790,4790,0,2157200,
ZINCMINI12JULFUT,I,20120715,13:59:48,103.5,103.5,103.5,103.5,0,7299,
ZINC12JULFUT,I,20120715,13:59:57,103.45,103.45,103.45,103.45,0,18340,
NATURALGAS12JULFUT,I,20120715,13:59:58,158.7,158.7,158.7,158.7,0,11212500,
GOLDGUINEA12JULFUT,I,20120715,13:59:58,23483,23483,23483,23483,0,243056,
----
Which is working good.
1. I think we need to run shell for as2ms in background as it refreshes complete windows screen. any idea?
2. we need to remove Amibroker related code to release it for production.
---------
I will test this in market hours tomorrow and let you know.
