Getting specific cells from multiple csv files

[nicoan]

Hello all

I hope someone can help me with this. I have different csv files with end of day data,
and I´m trying to get the close price for selected symbols in specific dates.

If possible, I don´t want to use macros. Just a function or something to get and handle
a ton of requests from hundreds of files as fast as possible.

I´ve attached the zip for you to see.
prices from multiple csv files.zip

Can you help?

[JBeaucaire]

CSV is not an Excel file format, it's a data storage format. Excel can't read from a CSV without opening it, this converting it into a spreadsheet format. At that point anything is possible.

So, if your data can ONLY come to you in CSV format, you will need to import those 100s of CSVs into a database spreadsheet of some kind, then you can do anything you want, referencing wise.

I do not believe you can easily import 100s of CSVs without a macro, and the macro won't be overly simple, nor overly complex. This is a pretty standard Excel task. As for what you do with the 100s of CSVs data once imported, that might complicate things, but not overly so. It's all a matter of clarity.

So I believe VBA is the path to your solution.

[webmayo]

There are two simple solutions. Neither involve Excel.

1. CsvFileSearch will search multiple csv files, and return the individual records in a table. I tested it using your sample files. It works fine.
http://genscriber.com/genapps/viewtopic.php?f=2&t=198

2. GenScriber will import multiple csv files. I imported your 110,000 records and searched them using the 'immediate filter'
http://genscriber.com/genapps/viewtopic.php?f=2&t=184

[nicoan]

Thank you both so much for replying.

The objective is to have a self updated list of most symbols in a data system and to pick and manage easily the data (EOD) we want.

I´m using MLDownloader to download the data and can get it in different ways exporting to ASCII, like txt for example (tab separated, custom char. separated, quotes, extension, etc).

Could we use a function like vlookup with txt files, or they necessarily have to be xls?

Not sure if MLD can export to excel format (I know I can enter XLS as an extension for export, but not sure if excel could read it).

(They also have the possibility to export everything into one file, but it would probably too big and unmanageable.)

[JBeaucaire]

The EXCEL solution would be to import all of those CSV files (they're always named the same thing, right?) into your workbook on individual reference sheets, then your formulas on the main sheet can do simple lookups. VBA would be the easiest way to repopulate those reference sheets in your workbook with the most current CSV data from a specific folder on your system.

If VBA is ok, I can help construct that for you.

[nicoan]

JBeaucaire, that sounds perfect. Thank you.

We could name the files the way we want with MLD. Currently the names are: Symbol.CSV (Aapl.CSV)


So in theory I could run an update (adding data to) all the CSVs, and when I open the control spreadsheet it could update itself?

I wonder how many files I could get into it... thinking that it would still be a problem updating 1000s of files.

What about updating by date? Like all symbols each day, this way is only one file each time and there´s no need to download those we already have.

I think I could do that with MLD.

What do you think?

[JBeaucaire]

I don't know the specifics of what the updated CSV files may or may not contain. The samples you provided had years and years of data. Is that not always the case?

My thought thus was that when you run the "update" macro, it looked in a specific folder for CSV files, if any are found it imports them into the master workbook. If that particular sheetname already exists in the workbook, it would REPLACE all the data on that sheet with the new incoming replacement data. Any sheets with data would keep that data unless a new CSV file is found to import.

So, after the initial update which would take some time to import that much data, future updates wouuld be quicker only updating the sheets for the incoming data.

Is this the wrong approach?

[nicoan]

The updates are just new rows in each file (as they are now), one for each day.
The length of the file depends on the symbol/company history. Some have only a few weeks, others have decades.

Let me explore a bit more the possibilities for updates with MLD. I´ll come back.
It would be inefficient to read all files again for updates (we have approx 7000 companies/files).

[JBeaucaire]

I'm going to stop you here. Excel is not a database program. You have too much data for simple spreadsheet referencing. You need to get your data into an Access or SQL database where you can then run any kind of reporting you wish on it.

You originally said 100s of files, which is borderline at best. 1000s of files is too much.

[nicoan]

Np. You helped a ton already.
Thanks.