Import Historical Stock Price Data

[london735]

Hello!
I need a macro that will be able to import a few text files into the workbook. This text import macro should open the ASCII files located in a specified folder one by one – leaving out the last column. The first column is the dates column and should be imported as text. In the attached worksheet I created two tabs – RAW and READY. I am not sure if there is any real need for the RAW tab as I thought it could be used for initial import of the text file for subsequent copying to the READY sheet. If this is not necessary the data should be imported starting from A1 cell on the READY sheet. The colored section of the READY sheet should be cleared after each import – more precisely – the area from A1 to E5000. And the last request - the name of the text file should be entered into the H1 cell on the READY tab after each data import.
Any help will be hugely appreciated!

PS: I have attached both the workbook and the data files, which are historical stock price history.

Please Note: I have also attached one more workbook (called NYSE) which could be modified to do the above procedure...It imports text files from a specified folder and finds the averages of the last column and then records it next to the name of the text file....I woudl modify it myself but I have very little practical knowledge of the VBA...I am hoping for some assistance here!)

[SAS888]

See attachment.
If name of the current file is absent on sheet "NYSE", that will is created new record. If record exists, data will is rewritten.

[london735]

thank you for your generous help. Can you also help me with the following task:

I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:

PROCESS macro:
Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.

IMPORT macro:
Imports the text from a specified folder and records the average of the last column along with the name of the text file. I need it to be plugged into the PROCESS macro with slight modification = it should import these text files into the A19 cell on the 1 tab and enter the name of the text file into the B17 cell on the same tab, there is no need to calculate the averages...

I would gladly do this all on my own but I feel that my knowledge is limited for this task. I really hope you will be able to help me!

Please see more clean workbooks that I attached

[SAS888]

See attachment. Press the button "ALL FILES PROCESSING".
I did not test. Please, check themselves. I have done so, as I have understood. Also, I optimized macro "PROCESS". What is not so? If you have a questions - I ready to answer.

[london735]

Dear Sas!!!
Thank you for this great macro that you created for me...Your PROCESS macro works but with slight errors so I am using my older PROCESS macro to test it on the data...Your IMPORT macro works perfectly.. i just want to ask you to clarify these lines in your code:

Range([A19], Cells(Rows.Count, "F")).ClearContents
This one clears the range from a19 to all the way below the sheet or to the end of the area which has NON-EMPTY cells? in our case it is all the way to F4949 ? This is some kind of a dynamic range?

[SAS888]

This code will clean contents within the range of "A19" before the last row of the sheet in column "F".

[SAS888]

Simply, there is much spare cycles in your macro "PROCESS". Also, "Select" - there is spare too.

[london735]

Thank you for your clarification.....I would gladly use a less complex PROCESS macro but the one you supplied does not seem to copy everything right....Can you please look into this?
one more question - is there a way to record text file names without the ".TXT" suffix?

[london735]

also can you tell me what this line does?
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2),Array(6, 2))
I thought it was the columns to be imported. How do I keep away the last column? I thought I should just remove the Array(6, 2) entry?
Thanks again!)))

[london735]

I have adopted your import macro for my exisitng worksheet but it imports the numbers as text...How do I import numbers as numbers?
Here is the import macro that I am using now:

Sub AllFilesProcessing()

    Dim FPath As String, FName As String: Application.ScreenUpdating = False: Application.DisplayAlerts = False
    FPath = "C:\MyTemp\" 'Insert path
    FName = Dir(FPath & "*.txt")
    Do While FName <> ""
        Range([A50], Cells(Rows.Count, "F")).ClearContents
        Workbooks.OpenText Filename:=FPath & FName, Origin:=xlWindows, DataType:=xlDelimited, _
            Space:=True, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 1))
        With ThisWorkbook.Sheets("1")
            Range([A1], Cells(Rows.Count, "F").End(xlUp)).Copy: .[A50].PasteSpecial paste:=xlPasteValues: .[B48] = FName
        End With
        ActiveWorkbook.Close
        GATHERCORRECTED
        FName = Dir
    Loop
    [C1].Select: MsgBox "Done"

End Sub

Thank you very much!