Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I am a stock trader. I download end of day data from eoddata.com. 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.
ADP,D,20090102,38.62,40.48,38.96,40.41,3530900
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.
AND
2. Volume must be greater than 500000.
AND
etc.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 ...
Thanks.
Randi
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.M2
0 
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.
0
if you dont mind using dos, try the following batch file
edited:
temporary fix solution, only can do against 2 criteria, close>open*1.12 AND volume>500k
assumption:
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.
::STOCK.BAT [CSVFILE as %1]
@echo off & setlocal enabledelayedexpansion::if "%~1"=="" %0 MS081222.txt
if "%~1"=="" echo USAGE: %0 [CSVFILE] & goto :eoffor /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]
:fixdollar
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!
)
goto:eof
to fully automate the process, you can use "wget" to download the csv file.
0
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:
ZHNE,02-Jan-2009,0.07,0.1,0.06,0.1,148800
(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.
Thanks.
Randi
0
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.
<date>,<ticker>,<open>,<high>,<low>,<close>,<volume>
01/05/09,AALI,10300.00,11750.00,10250.00,11750.00,2753500
01/05/09,ABBA,140.00,140.00,140.00,140.00,0
01/05/09,ABDA,190.00,190.00,190.00,190.00,0
01/05/09,ACES,740.00,740.00,740.00,740.00,1000000in your first post:
ADP,D,20090102,38.62,40.48,38.96,40.41,3530900
on second post:ZHNE,02-Jan-2009,0.07,0.1,0.06,0.1,148800
the format pattern doesnt match in the first postwhat 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.
0
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
ZHNE,02-Jan-2009,0.07,0.1,0.06,0.1,148800
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.)
Randi
0
(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.
#!/usr/bin/perluse 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.
0
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.M2
0
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 http://www.biterscripting.com/Downl... ...The .txt part needs to be entered ..."
0
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.
0  |
 Visual Basic Question
|
Batch File Password
|
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
