7 Replies Latest reply: Aug 30, 2013 9:19 AM by JonSamson
xomoxjewelry Level 1 Level 1 (0 points)

How can a live quote(price) be brought into cell and than used for calculations?


iMac, Mac OS X (10.6.8)
  • 1. Re: How can a live quote(price) be brought into cell and than used for calculations?
    Wayne Contello Level 6 Level 6 (13,615 points)

    Please add more detail for your request.

  • 2. Re: How can a live quote(price) be brought into cell and than used for calculations?
    Level 8 Level 8 (41,760 points)

    https://discussions.apple.com/thread/1884784

     

    Yvan KOENIG (VALLAURIS, France) vendredi 13 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My iDisk is : http://public.me.com/koenigyvan

  • 3. Re: How can a live quote(price) be brought into cell and than used for calculations?
    xomoxjewelry Level 1 Level 1 (0 points)

    Hi Wayne,

    Well,...let's say I'd like to know a current value of precious metals I have on hand,

    10 Oz of gold and 5 Oz of Silver incorporating a "live cell"in NUMBERS that is "linked" to a precious metals portal like KITCO.COM or Goldprice.org  so it constantly "feeds" the new price( let's say every 3 min) and other calculations like "loss","profit" are performed based on that changing value .

    It's almost like stock price,it goes up and down all day long and it affects the total value of your portfolio.

    Thanks for your inquiry

    Best Regards

    Marek Bugnacki

  • 4. Re: How can a live quote(price) be brought into cell and than used for calculations?
    Barry Level 7 Level 7 (29,180 points)

    Hi Marek,

     

    "Live cells" in Numbers can't be "linked" to external files or documents except in two circumstances: A hyperlink in a Numbers cell may be used to open and address an outgoing email message, or to open and display a webpage. With a little tweaking the webpage may be one located on the same computer as the Numbers document.

    Neither of those appear useful to your purpose.

     

    Have you checked the discussion linked in Yvan's post? It offers a method to fetch values from a website, and insert the values into cells in Numbers, using AppleScript.

     

    Isn't that more or less what you want to happen?

     

    Regards,

    Barry

  • 5. Re: How can a live quote(price) be brought into cell and than used for calculations?
    Level 8 Level 8 (41,760 points)

    If you accept to keep the spreadsheet open, it's quite easy to do that.

    It requires a script using the same structure than my old Autosave one.

    I'm not interested by gold trading so I have no idea of what you want to extract from the websource.

    May you post the code used to download the wanted value from the websource ?

    I would build a script using it.

     

    Yvan KOENIG (VALLAURIS, France) lundi 16 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

    My iDisk is : http://public.me.com/koenigyvan

  • 6. Re: How can a live quote(price) be brought into cell and than used for calculations?
    Level 8 Level 8 (41,760 points)

    It seems that the problem is not a really important one because you didn't answer to my question.

    I built a sample script.

    As I don't know the way to download datas from your specific resources, I choose to grab datas from stock quotes.

    Some variables define the:

    the doc name

    its path

    the names of sheets embedding tables

    the name of tables supposed to receive the datas.

     

    Example :

              set docName to "My quotes.numbers" (* target document *)

              set path_to_doc to "" & (path to desktop) & docName

    (*

    grab stock quotes from New York *)

              set tableName to "quotes New York" (* target table *)

              set sheetName to "quotes NewYork" (* target sheet *)

      (*

    grab stock quotes from Paris *)

              set tableName to "quotes Paris" (* target table *)

              set sheetName to "quotes Europe" (* target sheet *)

      (*

    grab stock quotes from Frankfurt *)

              set tableName to "quotes Frankfurt" (* target table *)

              set sheetName to "quotes Europe" (* target sheet *)

     

    The script was ready yesterdays but, as NYSE was closed I was unable to test it. Here it is.

    --{code}

    --[SCRIPT insert_Quotes stripped]

    (*

    Enregistrer le script en tant qu’application en arrière plan: insert_Quotes.app

    Vous pouvez l’exécuter lorsque vous le souhaitez

    ou l’ajouter à la liste des applications ouvertes au démarrage.

     

    À l'aide d'une instruction curl l’application télécharge  des informations depuis les sources Internet.

    En l'état il insère des cours boursiers dans le document Numbers défini

    par les variables docName et path_to_doc que vous pouvez éditer selon vos besoins.

    Cet script exemple extrait trois goupes de données.

    Il semble judicieux de séparer les titres en fonction de la monnaie utilisée pour afficher les cours.

    C’est juste par habitude que j'ai séparé les titres de Paris de ceux de Francfort.

     

    **********

     

    Save the script as an application running in the background : insert_Quotes.app

    You may run it when you want

    or add it to the list of applications opened on startup

     

    Using curl it download datas from Web pages.

    As is, stock quotes are inserted in the Numbers document defined

    by the variables docName and path_to_doc which you may edit to fit your needs.

    Here the sample script treat three sets of quotes.

    I felt fine to separate quotes whose currency is $ from those whose one is €.

    The split between Paris and Frankfurt is just an old habit.

     

    Yvan KOENIG (VALLAURIS, France)

    2009/01/29

    2009/09/20 : create variable to set parameters.

    I hope that it will help users to understand the way to use the script.

    2009/11/29 : take care of possible comma in expanded quote name

    2009/12/13 : enhance the treatment of possible comma in expanded quote name

    2012/01/16 : edit the curl requests and call by an idle handler

    *)

    --=====

     

    property liste1 : {}

    property liste2 : {}

     

    --=====

     

    on idle

              my main()

      (* Will save every 3 minutes but you may edit the instruction to fit your needs *)

              return 3 * minutes

    end idle

     

    --=====

     

    on main()

     

              set docName to "My quotes.numbers" (* target document *)

              set path_to_doc to "" & (path to desktop) & docName

     

              tell application "Numbers" to open path_to_doc

     

      (*

    grab stock quotes from New York *)

              set tableName to "quotes New York" (* target table *)

              set sheetName to "quotes NewYork" (* target sheet *)

     

              set t3 to "http://" & "download.finance.yahoo.com/d/quotes.csv?s="

              set parms to "AAPL,ARLP,GILD,VLCCF,BEGBX,ABT,ABX,BPT,BTE,D,DOM,DUK,E,ENB,ERF,XEL,AGG,BSV,VTI "

              set t4 to "&f=snxl1t1d1c1ohgv"

              set request to t3 & parms & t4

              set firstColumn to 1 (* column of the first target cell *)

              set firstRow to 2 (* row of the first target cell *)

              set timesList to {5} (* list of data fields containing time values *)

              set datesList to {6} (* list of data fields containing date values *)

              set NumbersList to {4, 7, 8, 9, 10, 11} (* list of data fields containing numeric values *)

              set dateFormat to "mdy" (* grabbed dates are in mm/dd/yyyy format *)

              my itsAquoteFile(dateFormat, my download(request), docName, sheetName, tableName, firstColumn, firstRow, timesList, datesList, NumbersList, 2)

     

      (*

    grab stock quotes from Paris *)

              set tableName to "quotes Paris" (* target table *)

              set sheetName to "quotes Europe" (* target sheet *)

     

              set t3 to "http://download.finance.yahoo.com/d/quotes.csv?s="

              set parms to "ACA.PA,ALU.PA,EDF.PA,FTE.PA,KN.PA,NEX.PA,SOI.PA,STM.PA"

              set t4 to "&f=snxl1t1d1c1ohgv"

              set request to t3 & parms & t4

              set firstColumn to 1 (* column of the first target cell *)

              set firstRow to 2 (* row of the first target cell *)

              set timesList to {5} (* list of data fields containing time values *)

              set datesList to {6} (* list of data fields containing date values *)

              set NumbersList to {4, 7, 8, 9, 10, 11} (* list of data fields containing numeric values *)

              set dateFormat to "mdy"

              my itsAquoteFile(dateFormat, my download(request), docName, sheetName, tableName, firstColumn, firstRow, timesList, datesList, NumbersList, 0)

     

      (*

    grab stock quotes from Frankfurt *)

              set tableName to "quotes Frankfurt" (* target table *)

              set sheetName to "quotes Europe" (* target sheet *)

              set t3 to "http://download.finance.yahoo.com/d/quotes.csv?s="

              set parms to "CBK.DE,CON.DE,DAI.DE,DBK.DE,DTE.DE,EOAN.DE,VOW.DE"

              set t4 to "&f=snxl1t1d1c1ohgv"

              set request to t3 & parms & t4

              set firstColumn to 1 (* column of the first target cell *)

              set firstRow to 2 (* row of the first target cell *)

              set timesList to {5} (* list of data fields containing time values *)

              set datesList to {6} (* list of data fields containing date values *)

              set NumbersList to {4, 7, 8, 9, 10, 11} (* list of data fields containing numeric values *)

              set dateFormat to "mdy"

              my itsAquoteFile(dateFormat, my download(request), docName, sheetName, tableName, firstColumn, firstRow, timesList, datesList, NumbersList, 0)

    end main

     

    --=====

     

    on download(t)

              my nettoie()

              set t to do shell script "curl " & quoted form of t

              my nettoie()

              return t

    end download

     

    --=====

     

    on itsAquoteFile(date_Format, texte, trueDoc, trueSheet, trueTable, trueColumnNum1, trueRowNum1, TimeList, DateList, NumberList, checkComma) (*

     

    date_Format : a string defining the source dateFormat ("dmy" or "mdy").

    texte : the datas grabbed from the Internet via curl

    trueDoc : the name of the target Numbers document

    trueSheet : the name of the target sheet in the target Numbers document

    trueTable : the name of the target table in the target sheet in the target Numbers document

    trueColumnNum1 : the number of the first column ot the target cells range

    trueRowNum1 : the number of the first row ot the target cells range 

    TimeList : a list of the indexes of datas which must be deciphered as time values

    DateList : a list of the indexes of datas which must be deciphered as date values

    NumberList : a list of the indexes of datas which must be deciphered as numeric values

    checkComma : the index of field needing special check for embedded comma ( 0 if there is no need to check )

     

    texte contains stock values, for instance, from USA (with t4 = "&f=snxl1t1d1c1ohgv"):

    "AAPL","Apple Inc.",96.35,"4:00pm","3/12/2009",0.00,N/A,N/A,N/A,650

    "ABT","ABBOTT LABORATORI",46.65,"4:00pm","3/12/2009",0.00,N/A,N/A,N/A,0

    "ABX","BARRICK GOLD CP",28.87,"4:01pm","3/12/2009",0.00,N/A,N/A,N/A,0

    "AGG","ISHARES BARCLAY",100.57,"4:00pm","3/12/2009",0.00,N/A,N/A,N/A,0

    "ARLP","Alliance Resource",27.17,"3:58pm","3/12/2009",0.00,N/A,N/A,N/A,0

     

    *)

              local texte, maybe, deci, delim, p, rowsCount, columnsCount, nbRows, nbColumns

     

      (* maybe is a catch all variable *)

              tell application "Numbers" to set maybe to name of every sheet of document trueDoc

              if maybe contains trueSheet then (*

    OK, the document contains the wanted sheet *)

                        tell application "Numbers" to set maybe to name of every table of sheet trueSheet of document trueDoc

                        if maybe contains trueTable then (*

    OK, the document contains the wanted table *)

                                  set {deci, delim, delim2} to my buildDelims(texte)

                                  set my liste1 to paragraphs of texte

                                  repeat with p in my liste1

                                            if p as text is not "" then

                                                      if (p does not contain quote) or (checkComma = 0) then

                                                                copy my decoupe(p, delim2) to end of my liste2

                                                      else

                                                                set try1 to my decoupe(p, delim2)

                                                                if (item checkComma of try1 starts with quote) and (item checkComma of try1 does not end with quote) then

                                                                          set try2 to {item 1 of try1, (item checkComma of try1 & delim2 & item (checkComma + 1) of try1)}

                                                                          repeat with i from (checkComma + 2) to count of try1

                                                                                    copy item i of try1 to end of try2

                                                                          end repeat

                                                                else

                                                                          set try2 to try1

                                                                end if -- (item 2 of try1 starts …

                                                                copy try2 to end of my liste2

                                                      end if -- (p does not …

                                            end if -- p as text …

                                  end repeat

     

                                  set rowsCount to count of my liste2

                                  set columnsCount to count of item 1 of my liste2

                                  tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

                                            set nbRows to row count

                                            set nbColumns to column count (*

    If row count is too small, add some of them *)

                                            if trueRowNum1 + rowsCount - 1 > nbRows then

                                                      repeat (trueRowNum1 + rowsCount - 1 - nbRows) times

      add row below row nbRows

                                                      end repeat

                                            end if --  trueRowNum1

                                            (*

    If column count is too small, add some of them *)

                                            if trueColumnNum1 + columnsCount - 1 > nbColumns then

                                                      repeat (trueColumnNum1 + columnsCount - 1 - nbColumns) times

      add column after column nbColumns

                                                      end repeat

                                            end if -- trueColumnNum1

                                            (*

    Decipher every row and fill the table accordingly *)

                                            repeat with y from 1 to rowsCount

                                                      tell row (trueRowNum1 + y - 1)

                                                                repeat with x from 1 to columnsCount

                                                                          try

                                                                                    set maybe to my supprime(item x of item y of my liste2, quote)

                                                                                    if x is in DateList then

                                                                                              set maybe to my normalizeDate(maybe, date_Format)

                                                                                    else if x is in TimeList then

                                                                                              set maybe to my remplace(maybe, "h", ":")

                                                                                    else

                                                                                              if x is in NumberList then set maybe to my normalize(maybe, deci)

                                                                                              if (x = 1) and maybe ends with ",PA" then set maybe to my remplace(maybe, ",PA", ".PA")

                                                                                    end if

                                                                          on error

                                                                                    set maybe to "n/a"

                                                                          end try

                                                                          set value of cell (trueColumnNum1 + x - 1) to maybe

                                                                end repeat -- x

                                                      end tell -- row

                                            end repeat -- y

                                  end tell -- table of sheet of document of application

                        else

                                  if my parle_Anglais then

                                            error "The sheet “" & trueSheet & "” of document “" & trueDoc & "” doesn’t embed the table “" & trueTable & "” !"

                                  else

                                            error "La feuille « " & trueSheet & " » du document « " & trueDoc & " » ne contient pas la table « " & trueTable & " » !"

                                  end if

                        end if -- maybe…table

              else

                        if my parle_Anglais then

                                  error "The  document “" & trueDoc & "” doesn’t embed the sheet “" & trueSheet & "” !"

                        else

                                  error "Le document « " & trueDoc & " » ne contient pas la feuille « " & trueSheet & " » !"

                        end if

              end if -- maybe…sheet

    end itsAquoteFile

     

    --=====

     

    on buildDelims(t)

              local de_ci, de_lim, de_lim2

              if character 2 of (0.5 as text) is "." then

                        set de_ci to "."

                        set de_lim to ","

              else

                        set de_ci to ","

                        set de_lim to ";"

              end if

              if t contains ";" then

                        set de_lim2 to ";"

              else

                        set de_lim2 to ","

              end if

              return {de_ci, de_lim, de_lim2}

    end buildDelims

     

    --=====

     

    on normalize(m, d6)

              if m starts with "+" then set m to text 2 thru -1 of m

              if (d6 is ",") and m contains "." then set m to my remplace(m, ".", ",")

              return m

    end normalize

     

    --=====

     

    on normalizeDate(dd, df)

              local x, m, d, y

              if dd contains "/" then

                        if df is "dmy" then

                                  set x to "31/12/1943"

                        else

                                  set x to "12/31/1943"

                        end if

                        try

                                  date x

                                  return dd

                        on error errMsg number errnbr

                                  set {m, d, y} to my decoupe(dd, "/")

                                  return my recolle({d, m, y}, "/")

                        end try

              else

                        return dd

              end if --  dd contains …

    end normalizeDate

     

    --=====

     

    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

     

    --=====

     

    on recolle(l, d)

              local oTIDs, t

              set oTIDs to AppleScript's text item delimiters

              set AppleScript's text item delimiters to d

              set t to "" & l

              set AppleScript's text item delimiters to oTIDs

              return t

    end recolle

     

    --=====

    (*

    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

              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

     

    --=====

     

    on nettoie()

              set my liste1 to {}

              set my liste2 to {}

    end nettoie

     

    --=====

     

    on parle_Anglais()

              return (do shell script "defaults read 'Apple Global Domain' AppleLocale") does not start with "fr_"

    end parle_Anglais

     

    --=====

    --[/SCRIPT]

    --{code}

     

    Yvan KOENIG (VALLAURIS, France) mardi 17 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

    My iDisk is : http://public.me.com/koenigyvan

  • 7. Re: How can a live quote(price) be brought into cell and than used for calculations?
    JonSamson Level 1 Level 1 (0 points)

    Has anyone figured out how make a simple quote retriever from Yahoo Finance, with the ability to determine which tags you want.

     

    Please see http://www.gummy-stuff.org/Yahoo-data.htm

     

    Basically, I want to have a column of stock symbols, and a cell that indicates which tags I am interested in.

     

    The script should populate Column B, C, etc with appropriate responses to the tags I have chosen.

     

    Would appreciate help. The script here does not seem to run on my computer.

     

    Thanks