How to grab web data into Numbers sheet

Below is a simple script that grabs the last prices from the linked website and populates a Numbers table.

1. Copy-paste into Script Editor (in Applications > Utilities).
2. In the first three lines change sheet, table name and target cell to match your setup.
3. With the Numbers document open, click the triangle 'Run' button in Script Editor.

(If "nothing happens" make sure Script Editor.app is checked at System Preferences > Security & Privacy > Privacy > Accessibility).

That's it! You should be up and running in about two minutes. If you update prices often then you can put the script in the Script menu as explained here. (You can also attach a keyboard shortcut by placing it in an Automator Service.)

SG

-- change values between "" in the first three lines to match your setup

set mySheetName to "Sheet 1"

set myTableName to "Coin Prices"

set tgtCell to "A2"

set theHtml to do shell script "curl -s " & quoted form of "https://www.worldcoinindex.com"

set text item delimiters to {"<tbody>", "</tbody>"}

set tableContents to theHtml'stext item 2 # item 2 is the body of the price table

set text item delimiters to {"<h2>"} # site uses new h2 for each currency

set tableChunks to tableContents'stext items 2 thru -1

set pasteStr to ""

repeat with aChunk in tableChunks

set text item delimiters to "><span>$ </span><span class=\"span\">"

tell aChunk's text item 1 to set {theSymbol, thePrice} to {first word, last word}

set pasteStr to pasteStr & theSymbol & tab & thePrice & return

end repeat

set the clipboard topasteStr

tell application "Numbers"

tell front document

tell sheetmySheetName to tell tablemyTableName

activate

set selection range to rangetgtCell

delay 0.3

tell application "System Events" to keystroke "v" using {option down, shift down, command down}

end tell

end tell

end tell

This is how I get stockmarket up to date data into my numbers sheets.

1. Open a Google Drive account, then create a Google docs spreadsheet.

2. In column one, I type, say, BHP.ax (for the Aussie market)

3. Setup the following columns to give you the information you need. I tried to paste an example for you but was not able to do so as I'm not that clever.

4. I then cut and paste the column information into my sheet which has matching data setup.

It takes about 30 seconds for me to update the 200 or so prices and 5 movements that I need.

Hope this is helpful to you. Works for me. Yahoo has an automatic update in Numbers but I think it only updates about once per day. Unsatisfactory, in my case.

mr.bodich wrote:

For example from this website https://www.worldcoinindex.com/

But looks like very complex solution, I need to learn several days how Apple Script works, never did that.

Running a script (as opposed to writing one) usually takes the click of a mouse or a choice from the menu. That takes less than a minute, not days.

However, for that particular site, have you considered just selecting the table with prices, type command-c to copy, clicking once in a Numbers cell and Edit > Paste and Match Style?

The result of doing that here is:

The prices are in column E. You can easily use them elsewhere in your document via VLOOKUP().

SG

Often it's possible to use a script to retrieve data from the web, as shown in this thread. Do your cryptocurrencies have a symbol on Yahoo! Finance? If so, what are the symbols? If not, where are you getting the quotes?

SG

For stock prices (as opposed to cryptocurrencies) I do believe the scripts in the linked thread are much easier than setting up formulas in Google Sheets and then copying information over from there. To update prices I just make a menu choice (because I've put the script in the Script Menu). No formulas. No Google account. Easily hundreds of price updates in a second or so.

But cryptocurrencies that aren't on Yahoo! Finance are a different matter.

SG

mr.bodich wrote:

trying to add something like switching to main sheet after prices updated.

You can do that with something like this. (In my document I named the sheet with with the retrieved prices "Prices" and the table on that sheet "Coin Prices"). You can easily change the script to match your setup.

No buttons in Numbers, but you can place the script in the menu for a quick update whenever you need. Or attach a keyboard shortcut. You can also make the script automatically run at set time intervals although I tend to think that's more trouble than it's worth.

Hope you find the posts here helpful.

SG

-- change values between "" in first three lines to match your setup

set mySheetName to "Prices"

set myTableName to "Coin Prices"

set tgtCell to "A2"

set theHtml to do shell script "curl -s " & quoted form of "https://www.worldcoinindex.com"

set text item delimiters to {"<tbody>", "</tbody>"}

set tableContents to theHtml'stext item 2 # item 2 is the body of the price table

set text item delimiters to {"<h2>"} # site uses new h2 for each currency

set tableChunks to tableContents'stext items 2 thru -1

set pasteStr to ""

repeat with aChunk in tableChunks

set text item delimiters to "><span>$ </span><span class=\"span\">"

tell aChunk's text item 1 to set {theSymbol, thePrice} to {first word, last word}

set pasteStr to pasteStr & theSymbol & tab & thePrice & return

end repeat

set the clipboard topasteStr

tell application "Numbers"

tell front document

tell active sheet to set currSheet to its name # save current location

tell sheetmySheetName to tell tablemyTableName

activate

set selection range to rangetgtCell

delay 0.3

tell application "System Events" to keystroke "v" using {option down, shift down, command down}

end tell

activate

set active sheet to sheetcurrSheet# go back to original location

end tell

end tell