1

Example: Google Outstanding Shares are 670,000,000 and this value is collected in a txt file from Yahoo Finance API via the below script. The issue is; that numbers WITH commas or spaces are a bloody nightmare for Excel's delimiter to understand when I pull this txt file into Excel.

NB: I need the script to bring this value and all others values back WITHOUT commas OR spaces. I need 670000000 so that Excel antiquated delimiter can put the data into columns.

I have asked this a few times. Can someone put me out of my misery please before the hernia goes again and the mac gets launched out of the window. Thank you.

cd desktop/quoteUpdate
while true
do
  curl -o quotes.txt -s "http://download.finance.yahoo.com/d/quotes.csv?s=goog,aapl&f=nsl1c1p2j2r"
  sed -i '.bak' 's/,/ /g' quotes.txt  # replace commas with spaces
  echo UPDATED:
  date
  sleep 10
done
1

1 Answer 1

2

Shame on Yahoo for invalid CSV

$ cat quotes.csv 
"Google Inc.","GOOG",501.79,+0.92,"+0.18%",   678,365,000,26.36
"Apple Inc.","AAPL",106.86,-2.94,"-2.68%",  5,864,839,000,17.02

$ sed 's/[[:blank:]]*\([[:digit:],]\+\)\(,[[:digit:].]\+\)/"\1"\2/' quotes.csv 
"Google Inc.","GOOG",501.79,+0.92,"+0.18%","678,365,000",26.36
"Apple Inc.","AAPL",106.86,-2.94,"-2.68%","5,864,839,000",17.02
7
  • Hi Glenn, how do I incorporate this into the script. I am not a programmer per say. Do I need to put both of these paragraphs in. Thanks Jan 15, 2015 at 21:49
  • Replace your sed command with this: sed -i 's/[[:blank:]]*\([[:digit:],]\+\)\(,[[:digit:].]\+\)/"\1"\2/' quotes.txt Jan 15, 2015 at 23:57
  • Glenn that code sadly does not work with the script. The values retrieved back to the txt file from Yahoo Finance API STILL come back again WITH the the commas. ie google outstanding shares are 670,000,000 instead of 670000000 which I need due to Excel poor delimiter. Can you advise, happy to send you a screenshot if you wish. Jan 16, 2015 at 13:48
  • my answer does not remove the commas, it quotes the field so you can import it into a spreadsheet as a valid CSV file. Jan 16, 2015 at 14:29
  • Thats not what I was looking for though. I could do that already. Its the value with the commas when it gets INTO the spreadsheet with the commas that is the issue. Excel wants to break 678,000,000 into 3 separate columns with its text wizard delimiter due to the commas (rendering the whole thing useless). I would like the values in the txt file to be without commas and spaces. ie 670,000,000 is 67000000 Jan 16, 2015 at 14:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .