covert data from excel spreadsheet to metstock format

anil and other senior member
can u help me.. by what software i can use to covert real time data from excel spread sheet to metatsock..

thanx

Hey vineet,message sent.

CV

Hi,

Can you share it with everybody in this forum ?

Thanks and Regards,
Snowy

how to covert data from excel spreadsheet to metstcok format ?

Read the downloader help, you can easily conver the data for metastock, downloader simply takes the data & makes the MS files. Remember not to include any quotes any where.

Dear Members
What excel does is to craete a text file which the equis downloader converts into metastock. the spreadsheet I am uploading for your convenience. One can see the VB code in the spread sheet and use it it needs the bhavcopy (NSE to be kept in C:\eod directory and the processed txt file is created in C:\Bcopy directory. Once u have the Bhavcopy in C:\eod directory and run the program it makes the file in C:\Bcopy which can be read by equis downloader. Give it a try and let me know. Since I do not find a way to upload the ecel sheet I am copying the VBA here itself. U can simply copy this in your Macro and use it.

Sub ASC2MS()
'
' Macro recorded 08/01/2005 by Manoj
'ActiveWorkbook.CreateBackup = True
'Dim Dte As String
CStart:
Set NewBook = Workbooks.Add
header = Array("<ticker>", "<date>", "<open>", "<high>", "<low>", "<close>", "<vol>")
Range("A1:G1") = header

Call ReadLog(Dte, Bname, MSTxt)
Dte = CDate(Dte) + 1
If Dte > Date Then GoTo EEE
Call BhavCopy(Dte, Bname, MSTxt)

'End If
If Bname = "" Then GoTo EndProg
MsgBox "XXX"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" + Bname, Destination:=Range("A2"))
.Name = "Bhav"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("G2").Select
ActiveSheet.Paste
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.NumberFormat = "yyyymmdd"
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("B:B").EntireColumn.AutoFit
ActiveWorkbook.SaveAs Filename:=MSTxt, FileFormat:=xlCSV, _
CreateBackup:=False
Range("A1").Select
UpDate:
Open "C:\Bcopy\Asc2Mslog.CSV" For Append As #1
If Dte <= Date Then Write #1, Dte, Bname, Now, MSTxt
Close #1
EEE:
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
If CDate(Dte) <= Date Then GoTo CStart
EndProg:
End Sub
Sub BhavCopy(Dte, Bname, MSTxt) ' creating a valid BhavCopy Name
If Dte > Date Then GoTo Endfile
Set Fs = CreateObject("Scripting.FileSystemObject")
'Bname = ""
'Dte = #1/5/2005#
Bflg = 1

On Error Resume Next 'ErrObject
Do While Bflg > 0
Lstart:
Bname = ""
If Weekday(Dte) = 1 Or Weekday(Dte) = 7 Then Dte = (Dte) + 1 'accounting SAT & Sun
If Weekday(Dte) = 1 Or Weekday(Dte) = 7 Then Dte = (Dte) + 1 'GoTo Nofile
If Weekday(Dte) = 1 Or Weekday(Dte) = 7 Then Dte = (Dte) + 1 'GoTo Nofile
If Dte > Date Then
MsgBox "Date not arrived", 0, Dte '
Bname = ""
MSTxt = ""
GoTo Endfile
End If
tt = Dte
tt = Format(tt, "dd-MMM-yyyy")
ddd = Left(tt, 2)
If ddd < 10 Then dd2 = Mid(Dte, 2, 1) ' To make one digit date
yr = Right(tt, 4)
mnth = UCase(Mid(tt, 4, 3))
MSTxt = "C:\Bcopy\MS" + ddd + mnth + yr + ".Txt"
Bname2 = "C:\Eod\cm" + dd2 + mnth + yr + "bhav.csv"
Bname1 = "C:\Eod\cm" + ddd + mnth + yr + "bhav.csv"
If ddd > 9 Then Bname = Bname1
If Fs.FileExists(Bname2) = True Then Bname = Bname2 'Check whether the file exists or not
If Fs.FileExists(Bname1) = True Then Bname = Bname1 'Check whether the file exists or not
If Fs.FileExists(Bname) = False Then
Dte = Dte + 1
GoTo Lstart
End If
'Err.Number = 0
'Open Bname For Input As #7
'If Err.Number = 53 Then ' If File Not Found
' Dte = (Dte) + 1
' GoTo Lstart
'End If
Nofile:
Bflg = 0
Loop
Endfile:
MsgBox MSTxt, 0, Bname
Close #7
End Sub
Sub ReadLog(Dte, Bname, MSTxt)
Open "C:\Bcopy\Asc2Mslog.CSV" For Input As #1
Input #1, dd1, BNM, dd2, MSNm
Do While Not EOF(1)
If EOF(1) = True Then GoTo NORead
Input #1, DD, Bname, dte2, MSTxt
Loop
NORead:
Dte = CDate(DD)
MsgBox Dte, 0, "Last Conversion Date"
Close #1
End Sub

File ASC2MSLod.csv structure:

LastDate, Bhavcopy,DOC,MStxtFile
#2005-02-15#,"C:\Eod\cm15FEB2005bhav.csv",#2005-02-15 17:06:27#,"C:\Bcopy\MS15FEB2005.Txt"

File Header.CSV structure:
<ticker>,<date>,<open>,<high>,<low>,<close>,<vol >

Sub ReadLog(Dte, Bname, MSTxt)
Open "C:\Bcopy\Asc2Mslog.CSV" For Input As #1
Input #1, dd1, BNM, dd2, MSNm
Do While Not EOF(1)
If EOF(1) = True Then GoTo NORead
Input #1, DD, Bname, dte2, MSTxt
Loop
NORead:
Dte = CDate(DD)
MsgBox Dte, 0, "Last Conversion Date"
Close #1
End Sub

File ASC2MSLod.csv structure:

LastDate, Bhavcopy,DOC,MStxtFile
#2005-02-15#,"C:\Eod\cm15FEB2005bhav.csv",#2005-02-15 17:06:27#,"C:\Bcopy\MS15FEB2005.Txt"

File Header.CSV structure:
<ticker>,<date>,<open>,<high>,<low>,<close>,<vol >
Reply With Quote

In This part of macro replay debag when running
Thanks for VBA
Harish Chheda

Dear Manoj Gold and Chheda,

Thanks for the programme.
I triewd it.
The debugger is showing synext error and other errors too.
In Manoj golds programme last four lines have some error.
In chheddas programmes too it is showing error. I had done cut - paste of the programme.
Can u please Guide furhter.
My email is s.parthsarthi@***************
I shall greatly appreciate ur kind help.

Thanks and regards.

Dear Manoj Gold and Harish,
Unfortunately, I am yet te recieve any response from u.
Can u put ur programme on VBA and try it.

regard from junky

Hi Guys, I M A Mcx Dealer.

Can Anyone Help Me In Getting The End Of Day Data For Mcx.