CSV Files

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

imtiaz107e6

Thread Starter
Joined
Apr 22, 2010
Messages
3
Hi!

I don't know whether this is the correct forum, but hope somebody can help.

I have about 220 separate CSV files of different companies stock historical data as follows:

date,open,high,low,close,volume

I download the daily End Of Day data in CSV which has the following:

Company Name,date,open,high,low,close,volume

Is there a way the daily company quotes can be updated automatically to each individual company's file?

I have no programming experience.

Any help will be appreciated.

Tks in advance.
 

imtiaz107e6

Thread Starter
Joined
Apr 22, 2010
Messages
3
What program are you using to collate the data?
Right now I use a software called winmerge where I open the EOD data in one pane and open the individual company file, one at a time in another pane, then copy and paste the info.

rgds
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Sorry I don't have any experience with winmerge. I am surprised that you are not Excel or Access to do this.
I would suggest that you create a new Thread with winmerge in the title to attract anyone with knowledge of it.
Or if possible add it to the Title of this thread.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Hi,
I use the code I have added to the attached file to read my csv files.
The code I found on different sites and have editted some parts myself.
You will still have to add some code of your own to specify the file type, separator to read it, but the import part will read any file for you.
Maybe you can out it to use, alternative if it doesn't work attach one or two of the csv files you have so that I can do the coding for you. :)
I use Excel 2003 but it will work under 2007 too. (y)
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
I know, but Winmerge is nothing more than a text comparisson tool.
So that's why I sent them the Execl sheet.
I could use the same code in a vbs script but then waht do they need, want?
Where does the collated data have to be stored, saved, shown?
Let's wait and see
 
Joined
Sep 24, 2007
Messages
869
It is possible to make a code that will open the EOD file, and to add the data to the specific company file, but you would need to have one more file that will specify the correspondence between the Name of the company in EOD file and the name of the file that contains this company's data.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Maybe the filename contains the company naem, so you could use that as additional data?
 

imtiaz107e6

Thread Starter
Joined
Apr 22, 2010
Messages
3
Hi,
I use the code I have added to the attached file to read my csv files.
The code I found on different sites and have editted some parts myself.
You will still have to add some code of your own to specify the file type, separator to read it, but the import part will read any file for you.
Maybe you can out it to use, alternative if it doesn't work attach one or two of the csv files you have so that I can do the coding for you. :)
I use Excel 2003 but it will work under 2007 too. (y)
Hi!

Tks for the reply. I do use excel but sorry you have to explain what I have to do with the file u sent.

I will clarify further. I use winmerge as I dont have an alternative. It helps me to look at the Master file in one pane and then I can open the other files one at a time in the other pane copy from the master and paste in the other files. I use the individual company named files with a charting software which requires files to be in CSV format.

I am attaching the Master file which contains the EOD data and some of the individual files of companies so that it will make it clear.

Only the date,open,high,low,close,volume data from the master has to be exported to the company's individual file.

Tks and Rgds.
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,896
If you are using Excel, why don't you do it all in an Excel workbook, including the sorting & Charting?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Here is a solution using a ms-dos mbatch file and a vbs script.
Run the batch file to start the thing:

The batch file AND the vbs script must be in the same foder (together) where the csv files to be processed are

Run the batch file and you'll have the masteryyyymmdd.csv file.
Notice that I turned around your date notation for sorting reasons

The bacth file will always delete an existing master*.csv file before starting.
It lists all csv files to a file and the vbs script is triggered.
This one reads the csvlist.lst and processes all the files one by one adding the name and symbol field as per your example.
Let me know if it does the job for you
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Just a small addtion.
The output file is now numbered Masterjjjjmmdd-000.csv
this is just in case the nr of records exceed 65000
Excel up to version 2003 only allows 65536 rows.
So now if the total nr of lines exceed 65000 the new mastwr output is creade and it starts again.

The result is shown in a dialog and the master files will be 001, 002 etc.
Default is 001 for less than 65000 records

I foyu don't want this, use the previous one and disregard
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Maybe I'm doing too much but since I work with these type of files I want to make sure it works all the time.

I have attached the zip file again. It contains an EOD folder with a batch file and a vbs script file
CSVMaster.bat
CSV2Master.vbs

The bat file you can use to run the vbs script.
To work correctly it needs to knwo where to read the csv files, and where to store the output.
The first time you run it it will promt to craete a INI file, well I think it explaings itself.
You will have to Select the Sourcefolder and for the Outpur you can either select or create a folder.
The INI file and the List file that will contain the csv files will always be stored in the same folder where the batch and vbs are found.
In this folder a _logs folder will be created and there a log sile will be or created or appenden with some info.

when you test it the vbs script will at the end show a dialog with the results.
If you edit the vbs script (Notepad is Okay) you can set ShowResults = False and then schedule the batch file to run at the end of the day.
The log file will show what is has doen.

The only think I havent doen is to move the read csv files to another location so that you won't be reading the same files over and over again.

You could add a line to the batch file to do this.
Keep in mind that it has to do this only in the script has run correctly, so before the first goto end statement in the batch file.

I added the Master20100429-001.csv which has been created with the script.

Hope I could explain my train of thought.;)
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top