0

Folks, I have asked this question 5 times on here and have received 5 separate bits of code from super users that have all *failed.

Problem: I have stock information being brought back into a txt file from Yahoo Finance's API.

The issue is: numerical strings brought back from such have commas and spaces in them which is a nightmare to delimit in Excel. Excel wants to split 670,000,000 into 3 separate values and split such across 3 separate columns which is a joke.

How do I get 670,000,000 to look like 670000000.

Who can solve this puzzle which so many have failed. This Script below is the latest to fail.

cd desktop/quoteUpdate
while true
do
 curl -o quotes.txt -s "http://download.finance.yahoo.com/d/quotes.csv?s=goog,aapl,avxl&f=nsj2l1"
 sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'
echo UPDATED:
date
sleep 5
done
2

4 Answers 4

1

I think I've got a solution for you....

cd desktop/quoteUpdate
while true
do
  curl  -s "http://download.finance.yahoo.com/d/quotes.csv?s=goog,aapl,avxl&f=nsj2l1"|sed -e 's/,/_/1' -e 's/,/_/1' -e's/\(.*\),/\1_/' -e 's/,//g' -e 's/_/,/g' > quotes.txt
  echo UPDATED:
    date
    sleep 5
  done
done

Give that a try.... You can append data to your quotes.txt file by simply changing the > quotes.txt to >> quotes.txt

0

From the top of my head this ugly solution should do the trick. Replace your sed line by the following:

awk 'BEGIN{FS="\"";OFS="\"";}{gsub(",","",$5);$5=","$5;print $0}' quotes.txt > UPDATED_quotes.txt

I'll update this post if I find a more elegant answer.

8
  • that removes the last comma from the line as well.
    – Optichip
    Jan 19, 2015 at 15:41
  • @Optichip What do you mean last comma?
    – maiki
    Jan 19, 2015 at 15:46
  • The last comma, before the 508.08 (Current stock price) is removed as well when I run your command.
    – Optichip
    Jan 19, 2015 at 15:46
  • @Optichip my understanding was that it was a requirement (eg: 678,365,000,508.08 should be transformed as 678365000508.08)
    – maiki
    Jan 19, 2015 at 15:49
  • Yes, maiki, that is, what the asker said -- commas to be removed. But that's not, what he meant :-) The last number must remain intact.
    – Mikhail T.
    Jan 19, 2015 at 16:05
0

What you are getting from Yahoo! is data in CSV-format. The format is too complex for bash (or even sed or awk) to properly parse in a one-liner. (Being full Turing-machine capable, any of the tools can do it, it will just not be pretty.)

Your immediate problem with the above script is that sed is not given any input to work on, so your script hangs (after curl finishes), because sed awaits on stdin. You, probably, want to append "quotes.txt" to sed's current command-line:

sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta' quotes.txt

But, once you deal with that, you'll still be unhappy, because the sed-command does not parse CSV-data as such. You'll want a CSV-processing tool of some kind -- search for a CSV-parsing package written for your favorite scripting language. In case of Tcl, CSV-parsing is available as part of tcllib, for example.

Finally, if you don't really need to properly handle all possibilities of a CSV, but only ensure, the numbers in the third column are understood by Excel, you can cheat with something like:

curl -o - -s "http://download.finance.yahoo.com/d/quotes.csv?s=goog,aapl,avxl&f=nsj2l1" | \
    awk -F, '{
        sub("^ *", "", $3);  # Start with 3rd field by stripping leading spaces
        for (i = 4; i < NF; i++) # Append other fields to 3rd - but the last one
            $3 = $3 $i;
        print $1 "," $2 "," $3 "," $NF
    }'

The above call sends data directly from curl into awk (bypassing the intermiediate file-creation). If you need the data in a file (such as for Excel to load), redirect awk's final output into it:

.... awk ..... > quotes.csv

Nothing bash-specific in this example, BTW -- any shell would do. Note, that this awk-script only modifies the third-column (appending subsequent "columns" to it except the very last -- the NF). Note also, that it will fail, if one of the corporation-names ever has a comma in its name. If this is a problem for you, it can be avoided by using quotes (") instead of commas to find the numbers that must be merged together... But best of all, again, would be to treat the data as the proper CSV by using CSV-aware extension for your favorite scripting language.

0

Use perl instead of sed

perl -pe 's/,(?=\d{3}(?![.]))//g'

replace comma (followed by tree digits with no trailing a decimal point) with nothing.

Oh, just saw you also want the spaces trimmed

perl -pe 's/,[ ]+/,/g;s/,(?=\d{3}(?![.]))//g'

 

This should be used in the framework in place of 'sed', like

#!/bin/sh
while true
do
  clear
  date
  curl -s "http://download.finance.yahoo.com/d/quotes.csv?s=goog,aapl,avxl&f=nsj2l1"|\
    perl -pe 's/,[ ]+/,/g;s/,(?=\d{3}(?![.]))//g'| tee quotes.csv
  cat quotes.csv| sed -e 's/,/\t/g'> quotes.xls
  sleep 5
done

Did you know that excel will readily open a csv file "as is"
if it has commas replaced with tabs and has the '.xls' extension.

2
  • Does not auto update every 5 seconds this code for some strange reason and does not do what im looking for Jan 19, 2015 at 18:06
  • @ChrisDormani It does now (after edits).
    – tschodt
    Jan 21, 2015 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.