Here is a solution using Excel macro. Create a excel macro and delete whatever in the Microsoft Visual Basic editor and paste the VBA code below and save it under .xlsm file in a folder. It will consolidate all the SESprice.dat file in the same folder as the .xlsm file in Sheet1 and format Sheet2 column A as text and key in all the tickers that need to be consolidated. Download the SESprice.dat from SGX website and give it a unique name with .dat extension.
https://www2.sgx.com/research-education/securities
The consolidated output will be in SGX_EOD_output.csv.
Excel VBA code
'Start a new Excel spreadsheet, click Macros button, View, Create
'Name the macros at SGX_EOD and press enter
'There should be Sub SGX_EOD() and End Sub. Delete them.
'Copy and paste these codes
'Put all the SESprice.dat and this macro in one folder
'Need to rename SESprice.dat files to some unique name
'The filename must have .dat extension. Otherwise, it will not be read
'In column A of Sheet2, format it to Text
'In Sheet2 of the macro workbook, put in SGX ticker starting from Row 1
Sub SGX_EOD()
Dim thispath As String
Dim inputfile As String
Dim outputfile As String
Dim eod_linein As String
Dim eod_lineout As String
Dim ticker As String
Dim date_str As String
Dim eod_array As Variant
Dim i As Integer, j As Integer
Dim found As Boolean
outputfile = "SGX_EOD_output.csv" 'If you want, you can rename it
thispath = ThisWorkbook.Path
ThisWorkbook.Sheets("Sheet1").Columns("A:A").Clear
FileList ("*.dat")
Open thispath & "\" & outputfile For Output As #1
'If do not need label then just comment out the following statement
Write #1, "<Ticker>,<D>,<YYMMDD>,<Open>,<High>,<Low>,<Close>,<Volume>"
For i = 1 To ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Open thispath & "\" & ThisWorkbook.Sheets("Sheet1").Cells(i, 1) For Input As #2
While Not EOF(2)
Line Input #2, eod_linein
If Len(eod_linein) > 0 Then
If IsNumeric(Mid(eod_linein, 1, 1)) Then
eod_linein = Replace(eod_linein, Chr(13), "")
eod_array = Split(eod_linein, ";")
eod_array(14) = Trim(eod_array(14))
ticker = eod_array(14)
found = False
For j = 1 To ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
If ticker = ThisWorkbook.Sheets("Sheet2").Cells(j, 1) Then
found = True
Exit For
End If
Next j
If found Then
date_str = Mid(eod_array(0), 3, 2) & Mid(eod_array(0), 6, 2) & Mid(eod_array(0), 9, 2)
eod_lineout = ticker & ",D," & date_str
If eod_array(8) = 0 Then 'No trade, set Open, High, Low to Last
eod_lineout = ticker & ",D," & date_str & "," & Abs(eod_array(6)) & "," & Abs(eod_array(6)) & "," & Abs(eod_array(6)) & "," & Abs(eod_array(6))
Else
eod_lineout = ticker & ",D," & date_str & "," & Abs(eod_array(12)) & "," & Abs(eod_array(4)) & "," & Abs(eod_array(5))
'Check for delay close
If Trim(eod_array(15)) <> "" Then
eod_lineout = eod_lineout & "," & Abs(eod_array(15))
Else
eod_lineout = eod_lineout & "," & Abs(eod_array(6))
End If
End If
eod_lineout = eod_lineout & "," & Abs(eod_array(8))
Write #1, eod_lineout
End If
End If
End If
Wend
Close #2
Next i
Close #1
End Sub
'Sub to read ".dat" files in current folder and store to Sheet1
Sub FileList(fltr As String)
Dim i As Integer
Dim fldr As String
Dim sTemp As String, sHldr As String
fldr = ThisWorkbook.Path
If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
sTemp = Dir(fldr & fltr)
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = sTemp
If sTemp = "" Then End
i = 2
Do
sHldr = Dir
If sHldr = "" Then Exit Do
ThisWorkbook.Sheets("Sheet1").Cells(i, 1) = sHldr
i = i + 1
Loop
End Sub
) fee for that. Thanks.
