Tom Gorham
Numbers doesn’t have the ability to pull live data from the Internet,but a short dabble in AppleScript can remedy that, as we explain…
I wrote about Numbers’ role as a powerful accounting program in a recent issue (see MacUser, 10 September, p80). In truth though, it’s probably being used far more widely as a personal finance tool.
Numbers includes a clutch of templates to help you track your spending, but the program has a weakness. Unlike many personal finance applications, it can’t update data automatically in real time. While some manual input is to be expected, if you were calculating your assets, you’d have to update things like share valuations automatically. While there are plenty of online sources of share prices to check against, Numbers, unlike Excel or even AppleWorks before it, can’t natively pull live data over the Internet into a spreadsheet.
However, as usual with all these things, I turned to AppleScript to help Numbers use live data from the Internet.
I can’t claim to be the first person to do this: there are plenty of examples of scripts to import online data into Numbers that can be found with a quick Google search. However, I found them too complicated – or too long – for my basic needs.
I wanted a simple 20-line script that would link Numbers and Safari to find a current share value – using Yahoo!’s excellent finance site (uk.finance.yahoo.com) as a source – and place it into a particular cell in a Numbers table. This table would calculate the change in value since the share was last checked, and the total profit and loss on an investment since it was bought.
To test this script, I set up a basic and, yes, sadly fictitious, table in a Numbers document comprising a list of shares, their ‘ticker’ reference, which is what Yahoo! uses to identify a share, plus empty columns for current and previous values, as well as details of the number of shares owned, the price at which they were bought and any nominal profit and loss. Only one column of cells – in column D – would be automatically entered by the script. Others, such as the profit and loss column, would be calculated within the table.
I then put together the following short script in AppleScript Editor:
tell application “Numbers”
tell table 1 of sheet 1 of document 1
set myRange to value of cells of range”C6:C11″
repeat with a from 1 to (count of myRange)
set StockCode to item a of myRange
tell application “Safari”
open location” HYPERLINK “http://uk.finance.yahoo.com/q/hp?s=” \t “_blank” http://uk.finance.yahoo.com/q/hp?s=” & StockCode
delay 3
set theSource to text of document 1
close document 1
set Opening to (offset of “.L” intheSource) + 14 as string
set Closing to (offset of “%)” intheSource) as string
set myText to text Opening thruClosing in theSource
set GrabbedWords to words ofmyText
set CurrentPrice to item 1 ofGrabbedWords
end tell
set value of cell (a + 5) of column “E” tovalue of cell (a + 5) of column “D”
set value of cell (a + 5) of column “D” toCurrentPrice
end repeat
end tell
end tell
The first variable in the script, myRange, grabs the contents of a specified column of cells in the Numbers document. These are the ‘ticker’ values that will be used as part of the URL that Safari will later open. The script then cycles through all the ticker values in a ‘repeat loop’. For each ticker, the relevant Yahoo! page is opened (with a short delay to ensure the page has fully loaded) and the text on this page is searched for the price data we’re looking for. ‘Scraping’ the text for this data is a bit of a fudge and relies on certain parts of the text being unchanged. But until Numbers lets us interrogate online data directly, it’s the only option.
How does it find the relevant text? AppleScript is famously poor at text manipulation, but it does have an Offset command, which allows you to locate the position of a specified string within given text.
In this script, I knew the stock price I was looking for was located between the first occurrence of “.L” in the text, and the first occurrence of “%)”, so the script looks for the offset of both these occurrences and shunts the text between them into a fresh variable.
To tidy up this selection slightly, I adjusted the position of the first offset to make it start 14 characters after the first occurrence of “.L”. That way, the resulting variable string comprises only a handful of items, including the time the stock price was calculated and the percentage rise or fall over the day. However, it was only the first item – the current stock price – that I wanted to use in the spreadsheet, so it was assigned its own variable, CurrentPrice.
As Safari opens a new window for each share being checked, the script includes a line to close the current window when it has grabbed the required data. This keeps things nice and tidy.
Once the item has been isolated, the script turns back to Numbers and enters the price into the relevant cell of the currently open spreadsheet. Before it does this, it moves the previous price to the corresponding column containing the past price, so current and past prices can be compared. To identify the correct cell to paste the past and current prices into, the script uses the value of the variable, ‘a’, which increments each time the script repeats through its loop, and adjusts it to place it in the correct position in the table, so it moves down through cells D6, D7, D8, and so on.
The result is a spreadsheet that updates with near-live stock prices as soon as the script is run. The script merely grabs basic price data and the spreadsheet is left to do any calculations, such as price movement and profit and loss. This table works well on its own, but would probably sit better as part of a wider personal finance spreadsheet. For example, you could add it to a document based on Numbers’ supplied ‘Net Assets’ template, so your personal financial situation could be accurate up to the minute.
If you’re running this script regularly, it’s obviously easier to run it within the application itself. The easiest way would be to add it to your own User Scripts directory in your user’s Library/Scripts/Numbers folder – you may need to create a new one if one doesn’t exist already. Next, open the AppleScript Editor application’s preferences, and under the General tab, choose ‘Show Script menu in menu bar’. The Script Menu will appear in the menu bar and you can select the script at any time from this menu.
More importantly for Numbers users who have no interest in stocks and shares, the basic example I’ve given here could be extrapolated to work with any sort of live data that needs to be taken from the Internet. It can be extended to other applications, too. I’ve shown in the past how Keynote slides can link from data produced by Numbers documents – so presentations could pull live data from websites for up-to-the minute information.
What I hope this little script shows is that while its critics may suggest that Numbers can’t retrieve data from the Internet, that’s not strictly true. And while I’d like the next version of the program to be able to do this sort of thing more directly just as Excel can, it does show what a little dabble with a built-in scripting language can produce.
