5 Replies Latest reply: Jul 22, 2010 2:35 PM by KOENIG Yvan
Big Burro Level 1 Level 1 (125 points)
Here is a very simple Apple script (thus somewhat constrained) derived from some very complicated French scripts posted here some time ago. Installation/usage instruction in the code below:

-----
(*
GetQuotes
AppleScript for Numbers - gets stock names from column 2 of row 2 to rowcount of the frontmost numbers' document, first sheet, first table, gets quotes for them from Yahoo and puts them in row 3.
To istall/use it:
- paste this text in AppleScript Editor, and save it as a script (any name) in your script folder (Library/Scripts)
- in Numbers open a document (single sheet, single table) containing stock symbols in the second column . To get the current quotes for them (placed in the third column) run the script from the Apple Script menu (this menu can be activated in AppleScript Editor preferences)

*)


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 application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable
set rowsCount to row count
end tell
end tell

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
set t4 to "&f=l1"
set request to t3 & parms & t4
end tell
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 texte
end tell
end tell

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
-----

Here is a sample file you could use this script on.

Image: !http://img.skitch.com/20100722-eu1f5ixdh1k6axxmcntmceu6gm.jpg!

Mac OS X (10.4.7)
  • Level 8 Level 8 (41,780 points)
    It seems that you missed several points.

    (1) the "French" script was designed to grab many more infos about a given quote.
    (2) it was designed to give usable results on systems using the decimal comma as well as on system using the decimal period.

    Yours grabs one info per quote and returns values with decimal period which are useless on system using the comma.

    To be able to use it on a French system,
    I had to add a lot of code so that the number of instructions is multiplied by two.

    --


    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

    --=====
    --


    Yvan KOENIG (VALLAURIS, France) jeudi 22 juillet 2010 11:34:57
  • Big Burro Level 1 Level 1 (125 points)
    +It seems that you missed several points.+

    allô - don't think so, your original scripts (lovely work though it was) were way overdone and complicated. And I never figured how to use them. The stuff below couldn't be simpler, is easy to understand and use, and it can be used to build something more ambitious if need be. BTW it will work on any digital system with minor changes just changing decimal indicator to the appropriate on in the returned quote.

    thanks for your work though .

    P,S For people implementing it please remove tell application "safari" .... end tell fragment, it unnecessarily closes an existing Safari window.
  • Level 8 Level 8 (41,780 points)
    For sure, it extracts eight infos per quote and is able to store each of them in a precise column of a precise table of a precise sheet.

    As it extracts dates, it take care of different formats.
    It take care of different decimal separator
    It also take care, on user request, of the fact that some quotes descriptors embed a comma which was fooling the beast.

    But, for sure, if you just need the value of the quote, your script is sufficient.
    A way to define the target table on the fly would be a fine enhancement.

    Yvan KOENIG (VALLAURIS, France) jeudi 22 juillet 2010 18:18:40
  • Big Burro Level 1 Level 1 (125 points)
    OK my source code again with the change discussed above implemented:


    (*
    GetQuotes
    AppleScript for Numbers - gets stock names from column 2 of row 2 to rowcount of the frontmost numbers' document, first sheet, first table, gets quotes for them from Yahoo and puts them in row 3.
    To istall/use it:
    paste this text in AppleScript Editor, and save it as a script (any name) in your script folder (Library/Scripts)
    in Numbers open a document (single sheet, single table) containing stock symbols in the second column . To get current quotes for them (placed in the third column) run the script from the Apple Script menu (this menu can be activated in AppleScript Editor preferences)
    *)
    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 application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable
    set rowsCount to row count
    end tell
    end tell

    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
    set t4 to "&f=l1"
    set request to t3 & parms & t4
    end tell
    set texte to download(request)

    tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable
    tell row (y)
    set value of cell (3) to texte
    end tell
    end tell

    end repeat
    say "get quotes done"
    end run

    on download(t)
    set t to do shell script "curl " & quoted form of t
    return t
    end download

  • Level 8 Level 8 (41,780 points)
    This one give us entire freedom for the location of the target table.

    --


    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

    --=====
    --


    Testing it, I encountered the problem which pushed me to allow several sources of quote values.

    Lenovo Group Limited (LNVGY) is unavailable from Yahoo web site

    Yvan KOENIG (VALLAURIS, France) jeudi 22 juillet 2010 23:34:32