Stock Exchanges EOD Data conversion program problems

Question

I use National Stock Exchange (India) EOD Data for personal investment decisions. I download the daily End of Day quote file through a software Utility NSE EOD Data Downloader v3.0. The file downloaded is a csv format text file and contains dates as a text string. This file is of variable length depending upon active quotes on a particular day. I process the downloaded file through excel macro- enabled file with a view to convert date text string to date format and convert it to suitable format to process in Access Data Base for investment decisions based on parameters like moving averages , High Low Max and Min values averages etc and input to Amibroker trader program . The Method follows is outlined below.

I was facing some difficulty in up-loading the files . Hence providing a drop box link to the following files samples in "EOD_Data,zip"

Sample EOD_Data.txt  "EQ_03AUG2015.txt"
Sample of EOD_Formatting.xlsm 
Sample file dly_nsedly_conversion.xlsx 
Sample of EOD_Converted.xlsx

        [EOD_Data,zip]  
<https://www.dropbox.com/sh/256mvvcnj6fhu20/AAD7nZpTHBuHfCpMNznr93PDa?dl=0>

1.) Excel Macro enabled file "EOD_Formatting' is opened containing command button linked to macro open_text_file

1. EOD Data File received on daily basis such as "EQ_03AUG2015.txt" is saved in c:\mydir\EOD_Data.
2. Excel Macro-enabled file EOD_Formatting.xlsm is opened and initiates following steps.

a) Opens an Input-Box to input the file name like "EQ_03AUG2015.txt" which will vary on daily basis. Path can be modified by the user as per his directory to store such files. This file is opened and contents copied.

b) In order not to disturb macro file, it opens another file "dly_nsedly_conversion.xlsx" . Firstly contents of text file copied in previous step are pasted on Sheet3 starting from Cell A1. Secondly it clears previous content on "NSE_DLY_RAW " sheet, otherwise there can be duplicate rows. Range upto H2000 is chosen because general Price file of National Stock Exchange contains less than 2000 quotes. Then it moves to sheet3 and determines range of filled cells and moves to "NSE_DLY_RAW" sheet to paste the selected range starting from cell A2 so that Header row is not disturbed. Also date column is transformed from text string to date format.

c) Transformed file in .xlsx format is saved to mydir\EOD_Converted_Date folder as EOD_Converted.xlsx file which can be directly imported in Access Database and through Access exported as csv file for input to Amibroker Trader Program. Code of the EOD_Formatting.xlsm is appended below.

Public fname As String

      Public Sub open_text_file()
         Dim wb1 As Excel.Workbook
         Dim wb2 As Excel.Workbook
         Dim filepath As String
         Dim r As Range, i As Long
         'path of the file whcih contains the file as 
         'received 'from 'software utility NSE EOD Data Downloader v3.0
         filepath = "C:\mydir\EOD_Data\"
      'Input filename like "EQ_04AUG2015.txt" to be processed by excel 
       'macro file
         On Error Resume Next
         fname = Application.InputBox("Enter the Filename", Type:=2)
           On Error GoTo 0
             Workbooks.OpenText Filename:=filepath & fname, _
             StartRow:=1, DataType:=xlDelimited, Comma:=True
             Cells.Select
             Selection.Copy
         'File no. of quotes vary daily. Further processing to transform 
          'date string from text to date format and variable length files
       Set wb2 = Workbooks.Open("C:\mydir\dly_nsedly_conversion.xlsx")
          Sheets("Sheet3").Select
          Range("A1").Select
          ActiveSheet.Paste
          Sheets("NSE_DLY_RAW").Select
       'It is desired to clear previous content otherwise there can be 
       'duplicate rows. H2000 chosen because general Price file _
       ' of National Stock Exchange contains less than 2000 quotes
           ActiveSheet.Range("A2:H2000").Select
              Selection.ClearContents
           Sheets("Sheet3").Select
           Dim rng1 As Range
           Dim rng2 As Range
           Dim rng3 As Range
         Set rng1 = Cells.Find("*", [A1], , , xlByRows, xlPrevious)
         Set rng2 = Cells.Find("*", [A1], , , xlByColumns, xlPrevious)
         If Not rng1 Is Nothing Then
          Set rng3 = Range([A1], Cells(rng1.Row, rng2.Column))
          Range([A1], Cells(rng1.Row, rng2.Column)).Select
          Selection.Copy
         End If
         Sheets("NSE_DLY_RAW").Select
           Range("A2").Select
           ActiveSheet.Paste
         LR = Cells(rows.Count, "B").End(xlUp).Row
         Range("B2:B" & LR).Select
         Dim c As Range
       For Each c In Selection.Cells
         c.value = DataSerial(Left(c.Value,4), Mid(c.Value, 5,2), Right_
      (c.Value,2))

      'Following line added only to enforce the format.
     c.NumberFormat = "dd/mm/yyyy"
      Next
      ' EOD_Converted File is suitable for input to Access Data base
      ' and    Amibroker with minor working
     ActiveWorkbook.SaveAs Filename:= _
     "C:\mydir\EOD_Converted_Data\EOD_Converted.xlsx", FileFormat:= _
     xlOpenXMLWorkbook, CreateBackup:=False
   Workbooks.Close
   End Sub

Though this program works for me, I am still working on following problems. a). If data received is less than 1500 quotes then blank rows remains after end of the data filled range. These empty rows hinders in choosing Security Symbol as Key Field in Access 2007 as it does not allow duplicate values or blank fields in key field. b) I want converted output file to take filename from filename inputted in the input box rather than a generic file name. I am at a beginner level in Excel VBA. I would appreciate any help to make this program more professional and user friendly and guiding me with some clues to solve the problems mentioned above. This program is helpful for an average non technical investor . Thanks.

PS: Internet connection has started working normally in our area now. As   

 'such uploading pictures of sample files.

Answer 1

This seems a little too complicated to me. Consider using R to process the original CSV file, save the transformed data directly to an Access -- or better yet SQL -- database via the RODBC library and then access the data directly via the ODBC plugins in AmiBroker.