How can a live quote(price) be brought into cell and than used for calculations?
How can a live quote(price) be brought into cell and than used for calculations?
iMac, Mac OS X (10.6.8)
Please add more detail for your request.
7 replies
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
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
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
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
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 belowrownbRows
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 aftercolumnnbColumns
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
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
How can a live quote(price) be brought into cell and than used for calculations?