-
All replies
-
Helpful answers
-
Jun 16, 2014 8:00 PM in response to SGIIIby tabbycat14,At the end of every day, I manually update, in one column only, about 60 stock prices (and volumes). Each price is linked to (MRQ) P/E, (TTM) P/E and about 20 others. So when I change a price in that one column it triggers change throughout the sheet.
Instead of having the manually automatic update , which is your Jan 15 script (worked fine), I thought if I could get stock prices to change every 2 or 3 minutes in Numbers, that would be great, not imperative.
When I run the Jan 15 script, I have 5 tables, 2 columns each, up to 20 rows, on one sheet. When the numbers appear on this one sheet, they also appropriately appear (linked) on the other sheets with the mass of data.
This whole exercise is a nice convenience, and saves me time after 4PM.
I feel like I have 2 left feet using automator. Applescript seems to be the easier.
-
Jun 16, 2014 9:57 PM in response to tabbycat14by SGIII,So you have one column of symbols in one table and ideally you want one column of prices in that same table to update automatically if possible?
If so, are the symbols in column A and prices in column B? Or is your setup different from that?
SG
-
Jun 17, 2014 7:46 AM in response to SGIIIby tabbycat14,Yes, to have the column of prices update automatically is ideal.
Column A has the symbols, column B the prices.
T14
-
Jun 17, 2014 10:29 AM in response to tabbycat14by SGIII,The on idle code seems to be broken. There should be way, if it won't update every few minutes as before, to have it update at a specified time every day. I'll experiment more and post.
SG
-
Jun 20, 2014 9:29 AM in response to tabbycat14by SGIII,Yes, to have the column of prices update automatically is ideal.
Column A has the symbols, column B the prices.
I posted a script to do this a few days ago but the support communities software update seems to have swallowed it, and a later attempt. Frustrating! Anyway, the script below should do what you want.
Copy into AppleScript Editor, hold down the option key and Save As a "stay open" Application. Then just run the application to start the automatic updates.
This assumes you have the symbols starting on the second row of the first column of the first table of the first sheet of the front document. You can change that after property t :
You can change the refresh interval (in seconds) by changing the number after return in return 120.
To turn off the notifications, delete or "comment out" (with a leading --) this line:
display notification "Stock data has been updated" with title "Numbers"
To stop the refresh, just quit the .app.
SG
(*
Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table at specified time interval when saved as a "stay open" application.
Place symbols in specified column starting at specified row number; no blanks; no footer row.
Retrieved data will be placed in columns immediately to the right of the symbols column.
By SGIII, 201406 v. 1.2
*)
--In the following line specify the types of data to retrieve in the order desired:
property quoteProperties : "l1" --other common choices: "r0e7e8s6"
(*
See https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty
Example: last trade price followed by pe followed by this year's estimated EPS ==> "l1roe7"
*)
--In the following line change the default values in black as needed to target the desired table:
property t : {targetDoc:1, targetSheet:1, targetTable:1, symbolsColNum:1, symbolsStartRow:2}
(*
Example: To populate a table named "Quotes" in sheet "Portfolio A" in document "Investments" with symbols starting on row 2 of column B==>
{targetDoc:"Investments.numbers", targetSheet:"Portfolio A", targetTable:"Quotes",symbolsColNum:2, symbolsStartRow:2}
*)
on idle
tell application "Numbers" to tell document (t's targetDoc) to tell sheet (t's targetSheet) to tell table (t's targetTable)
tell column (t's symbolsColNum) to set symbConcat to my joinList(value of cells (t's symbolsStartRow as number) thru (count cells), "+")
try
set the clipboard to my commaToTab(my getYData(symbConcat, quoteProperties))
on error
return --halt script if error getting Yahoo data
end try
set the selection range to cell (t's symbolsStartRow) of column ((t's symbolsColNum) + 1)
tell application "Numbers" to activate
tell application "System Events" to keystroke "v" using {option down, shift down, command down}
display notification "Stock data has been updated" with title "Numbers"
end tell
return 120
end idle
to getYData(qSymb, qProp) -- get Yahoo! data
try
set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"
set {symbStr, propStr} to {"s=" & qSymb, "&f=" & qProp}
set yData to do shell script "curl -s " & quoted form of (baseURL & symbStr & propStr)
if yData's text 1 thru 2 is "<!" then error --intercept Yahoo error page
if yData's text 1 thru 3 is "0.0" then error --a little more error-checking
return yData
on error
display alert "Trouble getting data from Yahoo! Check symbols. Ensure there are no blanks in the column and no footer rows, and that you have the right values in t's properties."
return
end try
end getYData
to joinList(aList, separator) --convert AS list to delimited string
set {oTid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, separator}
set lstStr to aList as string
set text item delimiters of AppleScript to oTid
return lstStr
end joinList
to commaToTab(str) -- Numbers 3 wants tab-separated for pasting
try
set {oTid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ","}
set strLst to text items of str
set AppleScript's text item delimiters to tab
set tsvStr to strLst as text
set AppleScript's text item delimiters to oTid
return tsvStr
on error
return
end try
end commaToTab
--end of script
-
Jun 20, 2014 9:35 AM in response to SGIIIby SGIII,Another try to post the script:
(*
Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table at specified time interval when saved as a "stay open" application.
Place symbols in specified column starting at specified row number; no blanks; no footer row.
Retrieved data will be placed in columns immediately to the right of the symbols column.
By SGIII, 201406 v. 1.2
*)
--In the following line specify the types of data to retrieve in the order desired:
property quoteProperties : "l1" --other common choices: "r0e7e8s6"
(*
See https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty
Example: last trade price followed by pe followed by this year's estimated EPS ==> "l1roe7"
*)
--In the following line change the default values in black as needed to target the desired table:
property t : {targetDoc:1, targetSheet:1, targetTable:1, symbolsColNum:1, symbolsStartRow:2}
(*
Example: To populate a table named "Quotes" in sheet "Portfolio A" in document "Investments" with symbols starting on row 2 of column B==>
{targetDoc:"Investments.numbers", targetSheet:"Portfolio A", targetTable:"Quotes",symbolsColNum:2, symbolsStartRow:2}
*)
on idle
tell application "Numbers" to tell document (t's targetDoc) to tell sheet (t's targetSheet) to tell table (t's targetTable)
tell column (t's symbolsColNum) to set symbConcat to my joinList(value of cells (t's symbolsStartRow as number) thru (count cells), "+")
try
set the clipboard to my commaToTab(my getYData(symbConcat, quoteProperties))
on error
return --halt script if error getting Yahoo data
end try
set the selection range to cell (t's symbolsStartRow) of column ((t's symbolsColNum) + 1)
tell application "Numbers" to activate
tell application "System Events" to keystroke "v" using {option down, shift down, command down}
display notification "Stock data has been updated" with title "Numbers"
end tell
return 120
end idle
to getYData(qSymb, qProp) -- get Yahoo! data
try
set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"
set {symbStr, propStr} to {"s=" & qSymb, "&f=" & qProp}
set yData to do shell script "curl -s " & quoted form of (baseURL & symbStr & propStr)
if yData's text 1 thru 2 is "<!" then error --intercept Yahoo error page
if yData's text 1 thru 3 is "0.0" then error --a little more error-checking
return yData
on error
display alert "Trouble getting data from Yahoo! Check symbols. Ensure there are no blanks in the column and no footer rows, and that you have the right values in t's properties."
return
end try
end getYData
to joinList(aList, separator) --convert AS list to delimited string
set {oTid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, separator}
set lstStr to aList as string
set text item delimiters of AppleScript to oTid
return lstStr
end joinList
to commaToTab(str) -- Numbers 3 wants tab-separated for pasting
try
set {oTid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ","}
set strLst to text items of str
set AppleScript's text item delimiters to tab
set tsvStr to strLst as text
set AppleScript's text item delimiters to oTid
return tsvStr
on error
return
end try
end commaToTab
--end of script
-
Jun 20, 2014 9:41 AM in response to SGIIIby SGIII,(* Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table at specified time interval when saved as a "stay open" application. Place symbols in specified column starting at specified row number; no blanks; no footer row. Retrieved data will be placed in columns immediately to the right of the symbols column. By SGIII, 201406 v. 1.2 *) --In the following line specify the types of data to retrieve in the order desired: property quoteProperties : "l1" --other common choices: "r0e7e8s6" (* See https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty Example: last trade price followed by pe followed by this year's estimated EPS ==> "l1roe7" *) --In the following line change the default values in black as needed to target the desired table: property t : {targetDoc:1, targetSheet:1, targetTable:1, symbolsColNum:1, symbolsStartRow:2} (* Example: To populate a table named "Quotes" in sheet "Portfolio A" in document "Investments" with symbols starting on row 2 of column B==> {targetDoc:"Investments.numbers", targetSheet:"Portfolio A", targetTable:"Quotes",symbolsColNum:2, symbolsStartRow:2} *) on idle tell application "Numbers" to tell document (t's targetDoc) to tell sheet (t's targetSheet) to tell table (t's targetTable) tell column (t's symbolsColNum) to set symbConcat to my joinList(value of cells (t's symbolsStartRow as number) thru (count cells), "+") try set the clipboard to my commaToTab(my getYData(symbConcat, quoteProperties)) on error return --halt script if error getting Yahoo data end try set the selection range to cell (t's symbolsStartRow) of column ((t's symbolsColNum) + 1) tell application "Numbers" to activate tell application "System Events" to keystroke "v" using {option down, shift down, command down} display notification "Stock data has been updated" with title "Numbers" end tell return 120 end idle to getYData(qSymb, qProp) -- get Yahoo! data try set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?" set {symbStr, propStr} to {"s=" & qSymb, "&f=" & qProp} set yData to do shell script "curl -s " & quoted form of (baseURL & symbStr & propStr) if yData's text 1 thru 2 is "<!" then error --intercept Yahoo error page if yData's text 1 thru 3 is "0.0" then error --a little more error-checking return yData on error display alert "Trouble getting data from Yahoo! Check symbols. Ensure there are no blanks in the column and no footer rows, and that you have the right values in t's properties." return end try end getYData to joinList(aList, separator) --convert AS list to delimited string set {oTid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, separator} set lstStr to aList as string set text item delimiters of AppleScript to oTid return lstStr end joinList to commaToTab(str) -- Numbers 3 wants tab-separated for pasting try set {oTid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ","} set strLst to text items of str set AppleScript's text item delimiters to tab set tsvStr to strLst as text set AppleScript's text item delimiters to oTid return tsvStr on error return end try end commaToTab --end of script -
Aug 31, 2014 11:33 AM in response to SGIIIby msantram,Hello SGIII
Great Script, thank you.
I followed the thread, and using the latest script you posted, created the Script as an Application, with "StartUp Screen" & "Stay Open After Run Handler" checked off. I even gave it an icon :-)
In my Numbers document, I created a new sheet labeled "Quotes" with a Table named "QUOTES". I added three columns: NAME, SYMBOL, & PRICE. I initially placed the sheet first, but moved it to the 5th spot as I wanted to maintain the existing sheet order.
The script runs, but does not successfully paste the stock values within the sheet. It will be in the clipboard, and in another application that I have open (including this text field!). I cannot seem to figure out why it's not saving, nor adding the additional columns to the sheet.
Any ideas on what I am missing?
Thx!
Mo
-
Aug 31, 2014 1:45 PM in response to msantramby SGIII,From your description (the script runs, but the values are placed in the expected place in Numbers) it sounds as if you need to adjust the:
property t : {targetDoc:1, targetSheet:1, targetTable:1, symbolsColNum:1, symbolsStartRow:2}
to something like this (substituting your actual document name):
property t : {targetDoc:"NameOfMyDocument.numbers", targetSheet:"Quotes", targetTable:"QUOTES", symbolsColNum:1, symbolsStartRow:2}
The script assumes symbols are placed in column A. so you might change the order of your table column headers to SYMBOL, NAME, and PRICE.
Post symptoms if this still doesn't work on your setup.
SG
-
Aug 31, 2014 6:03 PM in response to SGIIIby msantram,Thx SG
Sorry if I was not clear. I have the field values correct, but the issue is that import is not pasting the values into the spreadsheet. I am running OS X Yosemite, so perhaps that may explain why the script may not be pasting the values, but I am not sure.
property t : {targetDoc:"Quotes.numbers", targetSheet:"Quotes", targetTable:"QUOTES", symbolsColNum:2, symbolsStartRow:2}
Thx
M
-
Aug 31, 2014 9:13 PM in response to msantramby SGIII,Have you double-checked to make sure your accessibility controls are set to allow gui scripting?
In Mavericks that is done via System Preferences > Privacy & Security. It's easier to do than the explanation makes it seem. Presumably Yosemite is similar.
SG
-
Nov 12, 2014 9:19 PM in response to SGIIIby JMANTN,I too am having the same issue as msantram in that the application will run however I have to manually hit command V to paste the data.
I have enabled accessibility controls for not just the Script Editor but for the application itself (made from your code). I actually didn't make any additional changes to your last posted script and it runs just fine except I have to manually paste the information in. My table is empty except for four stocks that start in row 2 of the first column and I don't have column names as of yet if this helps and as I stated everything works except for the pasting command.
For what it's worth I'm running 10.10.1 Yosemite and have the most up to date version of Numbers installed.
-
Nov 12, 2014 10:06 PM in response to JMANTNby JMANTN,Sorry to double post but I couldn't edit my post above to add the following:
Also when calling what aspects of the stock to pull from: https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty is there a way to break up the output?
I'm trying to recreate a XLS I had in EXCEL 2013 as I no longer use Office and I had as an example the following layout:
Symbol Stock_Name #_of_Shares Last Price .....etc........
In that example I'd be pulling Symbol, Stock_Name, and Last Price from Yahoo while # of shares is something I had keyed in. I'd have several columns where I had formulas built in that didn't pull data from yahoo. With Excel I could easily have each column specifically pull data from yahoo which in some ways wasn't the best but I didn't require mine to update all that often.
Just wondering if there was an easy way to have it skip specific columns? If not I could still most likely manipulate the script to fit my needs or at the very least have multiple scripts going simply based off of your excellent work that would populate the columns needed.
And lastly what if I wanted to run your last script as a script instead of an application? If that would only require changing a few lines of code could you share that with me? Sorry learning as I go and when it comes to scripting numbers this is my first time.
-
Nov 13, 2014 8:42 AM in response to JMANTNby SGIII,I have to manually hit command V to paste the data.
It's possible that your system needs time to catch up with AppleScript before AppleScript attempts to paste the values.
Right after:
tell application "Numbers" to activate
try inserting the line:
delay 0.25
SG
-
Nov 13, 2014 9:16 AM in response to JMANTNby SGIII,an easy way to have it skip specific columns?
There are, of course, ways to do that. However, for simplicity and efficiency, you might think in terms of a downloaded "data" table and then separate table(s) that pull(s) values from there and applies formulas, etc.
To retrieve values in columns similar to your example layout you would use something like
property quoteProperties : "s0n0j2l1"
That's assuming by #_of_shares shares you mean shares outstanding, not shares owned. I've found it's often better to put n0 at the end instead of in the middle because the name in Yahoo sometimes has embedded columns that causes it to paste across multiple columns and thus disturb the alignment of the imported values. There are automated ways to deal with embedded commas, but I've found just putting the n0 last is a workable solution, something like:
property quoteProperties : "s0j2l1n0"
If you don't need the automated updating that the "application" provides, then remove:
on idle
return 120
end idle
Then you should be able to save it and run it as a regular script.
If you want to target just one column at a time, then you can put one term in the quoteProperties and change the 1 in :
set the selection range to cell (t's symbolsStartRow) of column ((t's symbolsColNum) + 1)
to the number of the column you are targeting.
Or, probably better, you could put the target column number in a property :
property t : {targetDoc:1, targetSheet:1, targetTable:1, symbolsColNum:1, symbolsStartRow:2, targetColumn:2}
And use:
set the selection range to cell (t's symbolsStartRow) of column (t's targetColumn)
SG