Getting Stock Quotes in Numbers

property theApp : "Numbers"
--=====
on run
 local texte, rowsCount, trueDoc, trueSheet, trueTable
 
 say "starting"
 set t3 to "http://" & "download.finance.yahoo.com/d/quotes.csv?s="
 
 tell application "Numbers"
 activate
 
 set trueDoc to name of front document
 set trueSheet to name of first sheet of document trueDoc
 set trueTable to name of first table of sheet trueSheet of document trueDoc
 tell document trueDoc to tell sheet trueSheet to tell table trueTable
 set rowsCount to row count
 end tell -- document…
 end tell -- Numbers
 
 repeat with y from 2 to rowsCount
 tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable
 tell row (y)
 set parms to get value of cell (2)
 end tell -- row
 end tell -- Numbers
 set t4 to "&f=l1"
 set request to t3 & parms & t4
 set texte to download(request)
 try
 tell application "Safari" to close window 1
 end try
 tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable
 tell row (y)
 set value of cell (3) to my stringtonum(texte)
 end tell -- row
 end tell -- Numbers
 
 end repeat
 beep 1
 say "get quotes done"
 
end run
on download(t)
 set t to do shell script "curl " & quoted form of t
 return t
end download
--=====
on stringtonum(value)
 
 if value contains space then set value to my supprime(value, space)
 try
 value as number
 return value
 on error
 set deci_loc to character 2 of ((1 / 2) as text)
 if deci_loc = "," then
 set alt_loc to "."
 else
 set alt_loc to ","
 end if
 set alt_loc2 to space
 if value contains space then set value to my supprime(value, space)
 if (value contains deci_loc) and (value contains alt_loc) then
 set liste1 to my decoupe(value, deci_loc)
 set liste2 to my decoupe(value, alt_loc)
 if (count of last item of liste1) > (count of last item of liste2) then
 set value to my supprime(value, deci_loc)
 set value to my remplace(value, alt_loc, deci_loc)
 else
 set value to my supprime(value, alt_loc)
 end if
 else
 set value to my remplace(value, alt_loc, deci_loc)
 end if
 end try
 return (value as number) as text
end stringtonum
--=====
on decoupe(t, d)
 local oTIDs, l
 set oTIDs to AppleScript's text item delimiters
 set AppleScript's text item delimiters to d
 set l to text items of t
 set AppleScript's text item delimiters to oTIDs
 return l
end decoupe
--=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
 local oTIDs, l
 set oTIDs to AppleScript's text item delimiters
 set AppleScript's text item delimiters to d1
 set l to text items of t
 set AppleScript's text item delimiters to d2
 set t to l as text
 set AppleScript's text item delimiters to oTIDs
 return t
end remplace
--=====
(*
removes every occurences of d in text t
*)
on supprime(t, d)
 local oTIDs, l
 set oTIDs to AppleScript's text item delimiters
 set AppleScript's text item delimiters to d
 set l to text items of t
 set AppleScript's text item delimiters to ""
 set t to l as text
 set AppleScript's text item delimiters to oTIDs
 return t
end supprime
--=====
--

property theApp : "Numbers"
--=====
on run
 local t3, rowsCount, trueDoc, trueSheet, trueTable, rname, rowNum1, colNum1, rowNum2, colNum2, rowsCount, y, parms, texte
 
 say "Let's go"
 set t3 to "http://" & "download.finance.yahoo.com/d/quotes.csv?s="
 
 set {trueDoc, trueSheet, trueTable, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
 (*
It would be easy to enhance the script using the cell's parameters returned by the handler 
This way the script will no longer be sticked to columns 2 & 3.
*)
 set colNum1 to 2
 set colNum2 to colNum1 + 1
 tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable
 set rowsCount to row count
 end tell -- Numbers
 
 repeat with y from 2 to rowsCount
 tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable
 set parms to get value of cell colNum1 of row y
 end tell -- Numbers
 if parms is not in {0.0, ""} then (*
Treats only rows embedding a quote symbol *)
 set texte to download(t3 & parms & "&f=l1")
 try
 tell application "Safari" to close window 1
 end try
 tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable
 set value of cell colNum2 of row y to my stringtonum(texte)
 end tell -- Numbers
 end if -- parms is not…
 end repeat
 
 beep 1
 say "that's all folks"
 
end run
--=====
on download(t)
 return do shell script "curl " & quoted form of t
end download
--=====
on stringto_num(lavaleur)
 local beurk, deci_loc, alt_loc
 (* here remove the thousands separators which I am aware of, minus the English comma *)
 repeat with beurk in {space, character id 160, "'"}
 if la_valeur contains beurk then set la_valeur to my supprime(la_valeur, beurk)
 end repeat
 if la_valeur starts with "+" then set la_valeur to text 2 thru -1 of la_valeur
 try
 la_valeur as number
 return la_valeur
 on error
 if 0.5 as text = "0,5" then
 set deci_loc to ","
 set alt_loc to "."
 else
 set deci_loc to "."
 set alt_loc to ","
 end if
 if (la_valeur contains deci_loc) and (la_valeur contains alt_loc) then
 if (count of last item of my decoupe(la_valeur, deci_loc)) > (count of last item of my decoupe(la_valeur, alt_loc)) then
 return my remplace(my supprime(la_valeur, deci_loc), alt_loc, deci_loc)
 else
 return my supprime(la_valeur, alt_loc)
 end if
 else
 return my remplace(la_valeur, alt_loc, deci_loc)
 end if
 end try
end stringtonum
--=====
(*
set {rowNum1, colNum1, rowNum2, colNum2} to my getCellsAddresses(dname,s_name,t_name,arange)
*)
on getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
 local two_Names, row_Num1, col_Num1, row_Num2, col_Num2
 tell application "Numbers"
 set d_Name to name of document d_Name (* useful if we passed a number *)
 tell document d_Name
 set s_Name to name of sheet s_Name (* useful if we passed a number *)
 tell sheet s_Name
 set t_Name to name of table t_Name (* useful if we passed a number *)
 end tell -- sheet
 end tell -- document
 end tell -- Numbers
 if r_Name contains ":" then
 set two_Names to my decoupe(r_Name, ":")
 set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, item 1 of two_Names)
 if item 2 of two_Names = item 1 of two_Names then
 set {row_Num2, col_Num2} to {row_Num1, col_Num1}
 else
 set {row_Num2, col_Num2} to my decipher(d_Name, s_Name, t_Name, item 2 of two_Names)
 end if
 else
 set {row_Num1, col_Num1} to my decipher(d_Name, s_Name, t_Name, r_Name)
 set {row_Num2, col_Num2} to {row_Num1, col_Num1}
 end if -- r_Name contains…
 return {row_Num1, col_Num1, row_Num2, col_Num2}
end getCellsAddresses
--=====
(*
set { dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
*)
on getSelParams()
 local r_Name, t_Name, s_Name, d_Name
 set {d_Name, s_Name, t_Name, r_Name} to my getSelection()
 
 if r_Name is missing value then
 if my parleAnglais() then
 error "No selected cells"
 else
 error "Il n'y a pas de cellule sélectionnée !"
 end if
 end if
 
 return {d_Name, s_Name, t_Name, r_Name} & my getCellsAddresses(d_Name, s_Name, t_Name, r_Name)
end getSelParams
--=====
(*
set {rowNumber, columnNumber} to my decipher(docName,sheetName,tableName,cellRef)
apply to named row or named column !
*)
on decipher(d, s, t, n)
 tell application "Numbers" to tell document d to tell sheet s to tell table t to ¬
 return {address of row of cell n, address of column of cell n}
end decipher
--=====
(*
set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
*)
on getSelection()
 local _, theRange, theTable, theSheet, theDoc, errMsg, errNum
 
 tell application "Numbers" to tell document 1
 repeat with i from 1 to the count of sheets
 tell sheet i
 set x to the count of tables
 if x > 0 then
 repeat with y from 1 to x
 try
 (selection range of table y) as text
 on error errMsg number errNum
 set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
 return {theDoc, theSheet, theTable, theRange}
 end try
 end repeat -- y
 end if -- x>0
 end tell -- sheet
 end repeat -- i
 end tell -- document
 return {missing value, missing value, missing value, missing value}
end getSelection
--=====
on parleAnglais()
 local z
 try
 tell application "Numbers" to set z to localized string "Cancel"
 on error
 set z to "Cancel"
 end try
 return (z is not "Annuler")
end parleAnglais
--=====
on decoupe(t, d)
 local oTIDs, l
 set oTIDs to AppleScript's text item delimiters
 set AppleScript's text item delimiters to d
 set l to text items of t
 set AppleScript's text item delimiters to oTIDs
 return l
end decoupe
--=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
 local oTIDs, l
 set oTIDs to AppleScript's text item delimiters
 set AppleScript's text item delimiters to d1
 set l to text items of t
 set AppleScript's text item delimiters to d2
 set t to l as text
 set AppleScript's text item delimiters to oTIDs
 return t
end remplace
--=====
(*
removes every occurences of d in text t
*)
on supprime(t, d)
 local oTIDs, l
 set oTIDs to AppleScript's text item delimiters
 set AppleScript's text item delimiters to d
 set l to text items of t
 set AppleScript's text item delimiters to ""
 set t to l as text
 set AppleScript's text item delimiters to oTIDs
 return t
end supprime
--=====
--