Process a downloaded CSV file ?

January 4, 2009 at 12:58:01
Specs: Windows Vista, Pentium - 1GB

I am a stock trader. I download end of day data from The file is a CSV file (Comma Separated Values). The file has the following format:

Symbol,Date,Open,High,Low,Close,Volume,Other information that I don't use.

Each line is one stock symbol. The following is one such row.


Until now, I have been opening the file with a spreadsheet (excel) program, and then sort/filter lines using formulas, such as following.

1. Close price must be 1.12 times Open price.
2. Volume must be greater than 500000.

Doing this manually every day (sometimes more than once a day) has been taking a lot of time. Can someone show me a way to automate this ?

All I want is to see a list of stock symbols that meet my criteria - usually only 5 or 6 symbols meet all my criteria. I use Windows. I do copy-and-paste formulas into Excel from the previous day's file, but it still is manual process and takes some time. I would rather start an automated process, let it work by itself, then go and get a cup of coffee instead of just sitting at my computer.

Any help will be appreciated ...


See More: Process a downloaded CSV file ?

Report •

January 5, 2009 at 01:37:07
Hi Randi,

I dunno Excel macros but probably someone here does.

What's your opinion of Rio Tinto?

If at first you don't succeed, you're about average.


Report •

January 5, 2009 at 09:39:18
If you want to simplify and automate this process, then you'll want to use some sort of scripting language.

What scripting language(s) do you know? My preference would be to use Perl.

You also need to make a decision on how you want to handle the floating point number from the multiplication by 1.12. Do you want them truncated to the 2 decimal places or rounded up or rounded down to the 2 decimal places. Rounding down would have the same results as truncating. You may also need to make similar decisions on the other criteria that you haven't specified.

Report •

January 5, 2009 at 10:25:43
if you dont mind using dos, try the following batch file

temporary fix solution, only can do against 2 criteria, close>open*1.12 AND volume>500k
Open - column 3
Close - column 6
Volume - column 7
adjust it in the for loop if pattern is not the same.
use wordpad to copy & paste, then save as text format.

@echo off & setlocal enabledelayedexpansion

::if "%~1"=="" %0 MS081222.txt
if "%~1"=="" echo USAGE: %0 [CSVFILE] & goto :eof

for /f "skip=1 delims= " %%L in (%1) do (
for /f "tokens=3,6,7 delims=," %%a in ("%%L") do (
call :fixdollar %%a & set /a open=!fixdollar!*112
call :fixdollar %%b & set /a close=!fixdollar!*100
if %%c gtr 500000 if !close! gtr !open! echo %%L
goto :eof

::fixdollar [dollar as %1]
set fixdollar=0
for /f "tokens=1* delims=." %%a in ("%1") do (
if "%%~b"=="" (set cents=00) else set cents=%%b
for /L %%c in (0,1,9) do if !cents!==%%c set cents=0!cents!
set fixdollar=%%a!cents!

to fully automate the process, you can use "wget" to download the csv file.

Report •

Related Solutions

January 7, 2009 at 07:09:22
Hi Mechanix2go, FishMonger: Thanks for you suggestions.

Reno: I liked your batch script. But I am getting the following error on several lines in the csv file.

Invalid number. Numeric constants are either decimal <17>, hexadecimal <0x11>, or octal <021>.

Several data lines produce this error. One such data line:


(DOS won't allow me to copy and paste, so I am typing verbatim-hopefully, without any mistakes !)

Suggestions ?

Also, would dos allow me to download the file from internet ? I know you suggested wget, but I would rather do it all in one place so that I need no manual interface.


Report •

January 7, 2009 at 07:42:23
i tested against my data and works fine. yeah i know, working on my data doesnt guarantee to work on your data. if you could, please post the complete first 10 line of your data. including header.


in your first post:


on second post:

the format pattern doesnt match in the first post

what i miss out in the code is price can go below $1, and price can have 1 decimal point and 2 decimal point.

and i also miss one point in your first post that requires "another criteria(s)"
if the criteria(s) is dynamic depending on situation, then IMO batch is not going to work well. what other programming language are you good at? and what solution do you have so far?

please refer to post#3 for temporary fix solution. it only works with 2 criteria that is hardcoded. use wordpad to copy & paste.

what i am working at the moment, is using batchfile to download the data, then concatenate all the CSV file into one big file (around 3-5 years data) then use MS. ACCESS to link into the BIG CSV file. and later use SQL query to manipulate data.

Report •

January 7, 2009 at 11:52:38

Reno: I wish I could attach the entire one day's file, it is not that big, but I am afraid it is probably copyrighted by the service provider.

The correct row is


My original post contained D, because I was playing with the file. There is no D, there actually a date in its place. (I don't use that column at all.)


Report •

January 7, 2009 at 14:06:58
(DOS won't allow me to copy and paste, so I am typing verbatim-hopefully, without any mistakes !)

You need to enable "QuickEdit Mode" on the Options tab of the Properties for the cmd/DOS window.

Here's a Perl example that does the same as reno's batch file.


use strict;
use warnings;

my $csvfile = $ARGV[0] || die "USAGE: $0 <csvfile>\n";

open my $stock, '<', $csvfile or die "can't open '$csvfile' $!";
while( <$stock> ) {
my ($open, $close, $volume) = (split(/,/, $_))[2,5,6];
print if $volume > 500000 and $close > sprintf("%.2f", $open * 1.12);

There are several methods for retrieving the file in the same script and most are very easy. That part depends on the type of access you need to use i.e., ftp, sftp, http, etc.

Adding the additional conditions is also very easy.

Report •

January 8, 2009 at 05:59:20
A CMD window is NOT DOS.

You can copy what's in a CMD window by left clicking, holding and dragging to 'paint' your text; then press enter.

If at first you don't succeed, you're about average.


Report •

January 8, 2009 at 12:38:15

I had posted this question on another site. A solution was proposed. Here is an excerpt.

" ... for how to process a csv (comma separated values) file ... see ...The .txt part needs to be entered ..."

Report •

January 8, 2009 at 12:50:17
So, it needed 20 lines to do what the Perl script did in 4 lines.

Also, the conditions in that script didn't meet the requirements that you told us you needed.

Report •

Ask Question