Automated Bitcoin price in numbers

Here is an AppleScript gist that will retrieve the value of just about any cryptocurrency and add it to Numbers. It uses the API at Cryptonator API endpoint.

To run this you must have the JSON Helper for AppleScriptrunning (free on the MacOS app store), as well as numbers open to a sheet titled "Overview" that contains a table called "Coin Values (USD)" which has the following format:

|SYMBOL|PRICE|LAST_UPDATED_DATE|

where SYMBOL is the ticker symbol for the currency you want to fetch ('BTC'). The Price and LAST_UPDATED_DATE columns will be filled in by the script.

A more concrete example:

|BTC|blank|blank|

|ETH|blank|blank|

|LTC|blank|blank|

will fetch BTC, ETH, LTC and fill in the price and last updated columns.

Hey, a picture is worth 1000 words:

You can edit the code to adjust the base currency, table names and such.

Hi Skootle,

Your formula:

…will produce an error message. The syntax is correct, but what is the 'stock' that has the symbol "ETHUSD=X"?

I have seen reports here that the STOCK function will retrieve prices for some of the crypto currencies. If you click an empty cell on a table, type an = sign to open the Function Browser, then type STO in the search box, you should see a short list in the browser that includes the STOCK function. Click on that, and you'll see a description of the function, including its syntax.

STOCK was added to Numbers 4; I'm running Numbers 5, so I'm not able to pull that description up here. But I suspect you will find the syntax to be pretty close to this:

STOCK(symbol,attribute)

"symbol" means the exchange ticker symbol for the security. Most are three letter or four letter strings in CAPS. The symbol must be enclosed in double quotes. And there must be only one symbol in the function's arguments.

"attribute" may have a different label, but it will be a menu whose choices answer the question "What do you want to know about this security?"

Filled in, your formula could look like this: STOCK("ETH",price)

The CURRENCY function is a more recent addition to Numbers. Initially, BITCOIN ("BTC") was the only crypto currency supported by this function, but others may have been added since its release.

The syntax is different from that of STOCK in that it requires two symbols, and both must be the ticker symbols for a currency. As with STOCK, the symbols must be enclosed in double quotes, and as is usual in functions requiring more than one argument, the arguments must be separated by a comma (in regions where the decimal separator is a period) or by a semi colon (In regions where the decimal separator is a comma).

Here's an example, from SGIII's post above, showing a correctly composed formula to get the price of bitcoin ("BTC") in US Dollars ("USD")

=CURRENCY("BTC","USD",price)

Regards,

Barry

Thanks for that.

Note that in the latest versions of Numbers you can now retrieve stock and currency data via formula, similar to Google sheets.

The data comes from Yahoo! Finance. So for Bitcoin you could do something like this:

=CURRENCY("BTC","USD",price)

SG

I couldn’t achieve this in Numbers, so I decided to use Google sheets instead.

This is the basic method I used to extract live coin prices into a useable number for calculations. Two methods, one using an exchange API eg. BTCMarkets for Australia, the second method loads prices from coinmarketcap for the alt coins.

Method 1 - API

/// text in grey is what you paste into the cell within Google Sheets

/// CELL A2 - Call in the BTCMarkets API or update with exchange of your choice.

=IMPORTDATA("https://api.btcmarkets.net/market/BTC/AUD/tick")

/// CELL A1 - Extract last price from API

=SUM(SPLIT(A4,CONCATENATE(SPLIT(A4,”.0123456789"))))

/// Cell A1 now displays the "lastPrice" in a number format that can be applied to Sums etc.

/// You can also call asking prices or anything else the API loads up (change cell no. accordingly)

Method 2 - for other alt coins…

/// Call the Coinmarketcap page for the coin you want.

/// eg. Bitcoin Cash would be https://coinmarketcap.com/currencies/bitcoin-cash/

/// eg. NEO would be https://coinmarketcap.com/currencies/neo/

/// Cell B1 - display USD value of coin

https://coinmarketcap.com/currencies/neo/

/// Cell B2 - load the coin's price

=IMPORTXML(B1,”//span[@class='text-large']")

/// Cell B3 - For currency conversion from USD to AUD for example

=B2*GoogleFinance("CURRENCY:USDAUD")

This is a basic breakdown. I put all this stuff on a seperate sheet, then call it back into my main spreadsheet with calculations, charts and a neat presentation etc.

Hope this helps, good luck.

Zim.

Awesome- thanks for this! The script is working great for me. Did you write it? I did have to add spacer rows to my spreadsheet to get it to work properly (kept skipping alternate rows).

The API isn't supporting all of the alts unfortunately. Would this script work for other crypto screener site APIs?

Hi all, frustrating that the CURRENCY formula in Numbers doesn't "just work" for other currencies such as ETH, XLM, etc, even though they do exist in Yahoo Finance charts.

@cmikeb1 looks like I will have to give your script a go, cheers.

Hi Zim, when you say "I put all this stuff on a seperate sheet, then call it back into my main spreadsheet with calculations, charts and a neat presentation etc.", do you mean that you somehow integrate the Google sheet with a Numbers sheet? (awesome if so!)

Or did you completely give up on Numbers due to it's limited range of currencies?

Did you try cmikeb1's script method above at all?

I'd really like to achieve this in Numbers somehow rather than leave it and go down the Google sheets route. (Even though it is more powerful in it's capabilities in this area currently).

CoinBunny, could you maybe elaborate? I tried CURRENCY("ltc-eur","price") and CURRENCY("ltc-eur","eur", "price") and CURRENCY("ltc","eur","price") but none of them work. Did this work for you?

EDIT:

Supposed to use it with STOCK like this:STOCK("ltc-eur",price). Thank you!

Nah it does work, just differently. Either Yahoo or Numbers (wrongly?) considers litecoin, ethereum and so on to be stocks instead of currencies. Therefore the proper way to get data for these assets would be to use the STOCK function instead of the CURRENCY function. Here's an example that worked for me, for let's say NEO, because that one is even more unknown: STOCK("neo-eur",price). Make sure to type the things out, if you let autocorrect do this it won't recognise some assets.

Colinowww wrote:

.. use the STOCK function instead of the CURRENCY function. Here's an example that worked for me, for let's say NEO... STOCK("neo-eur",price).

Hmm that's interesting. Yep I tried your suggestion for LTC and it does indeed work. However for BTC, *either* CURRENCY or STOCK functions work:

CURRENCY("BTC","USD",price)

STOCK("BTC-USD",price)

.. but they produce slightly different results, i.e. at the time of writing this, the CURRENCY formula returns USD$14,925 and the STOCK formula returns USD$14,976.

Hey Colin,

I've tried the exact same line as you, even copy and pasted it but its giving me an orange triangle saying 'argument 1 is invalid'

I'm not really sure what to do and I'm on the phone with apple support but they barely know what Bitcoin is, let alone ETH or any other crypto. I'm trying to add ETH, XLM, XRP and TRX, but i've tried testing the same with LTC but to no avail.

I am using this apple script, and it works for 6 of the coins I have listed, but not for the others. I have tried to plug in Coinmarketcaps API ticker but it errors. Do you have any ideas how to get the others working? The coins that are not populating are XLM, REQ, COSS, XRB, ETH, EOS, PRL, BNTY, UTK, DBC, XVG, TRX, LEND, and BNB. I thought maybe because it's not translating these into USD on Cryptonator? The other thought I had was to add in the individual tickers for these coins, but I am not sure where to add that into the script. Any help would be much appreciated! Thanks!

Barry wrote:

STOCK(symbol,attribute)

"symbol" means the exchange ticker symbol for the security. Most are three letter or four letter strings in CAPS. The symbol must be enclosed in double quotes. And there must be only one symbol in the function's arguments.

Hi Barry, as Colinowww points out above, it seems the STOCK formula can also be used with two crypto currencies, by separating them with a dash, for example:

=STOCK("BTC-USD",price)

This works, but returns a slightly different result than:

=CURRENCY("BTC","USD",price)

I've yet to work out why the difference in results. It may be because Yahoo is pulling this data from 2 different sources.

Also the CURRENCY formula doesn't work with alt currencies, ETH for example (the second largest crypto currency after BTC), so I am hoping that the STOCK formula result is correct. It does seem to be closer to the close of prior day if I check on a few exchanges.

I was having many issues but you just need to type everything out. Try this, =STOCK "BTC-USD", "PRICE")

this works with the rest of the cryptocurrencies as well, so for ETH, LTC, TRX, etc.

=STOCK "ETH-USD", "PRICE")

=STOCK "TRX-USD", "PRICE")

you can even try copy and pasting one of the lines above and see what happens.

Hi Barry,

Yes, that worked changing to Stock; however, it will not read the price attribute for some reason. BTC works great, but trying to also add Ethereum and Litecoin. Any ideas?