Feeding Exchange Rate into Numbers

tokamac wrote:

Actually the conversion from "." to "," does not work:

error "Can’t make \"1.\" into type number." number -1700 from "1." tonumber

Well, actually, it does work, on some setups. Your setup is probably different. Sorry you're having trouble. Perhaps you can give more details on your setup and also what version of Numbers you are using.

I've found that with Numbers 4 sometimes a script doesn't work as expected, but quitting and relaunching both Numbers and Script Editor clears up the problem.

Post back with more specifics on your problem.

SG

Thank you. I'm on the latest Numbers version (4.0.5) on macOS Sierra 10.12.3

I tried everything you said with no success.

My system is in French, so decimal separator is a comma. When the OS is switched to English, the script still doesn't work because the decimal number separator in Preferences > Language & Region > Advanced

is still a distinct setting from the rest of the UI and remains a comma.

When this decimal number separator in that Pref pane is changed to a decimal point (whatever the language of the OS) the script indeed works but it pastes a left-aligned string (not a number) in the spreadsheet. So this is not a solution.

In French (in a language where the decimal separator is set to a comma in macOS) AppleScript treats any number with a decimal point as a string, and can't apply a mathematical operation to it.

Yes, my cell is already in currency in euro.

I'm not code-savvy but I groped for a solution and found a code which works :

set quoteProperties to "l1"

set currencies to "USDAUD=X"

set {s, t, c} to {"Sheet 1", "Table 1", "B2"}

tell application "Numbers" to tell front document's sheet s's table t

set theRate to my getYDATA(currencies, quoteProperties)

set theRate to my pointToComma(theRate)

set cell c's value to theRate

end tell

to getYDATA(symb, qProp)

set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"

set symbStr to "s=" & symb

set propStr to "&f=" & qProp

do shell script "curl -s " & "'" & baseURL & symbStr & propStr & "'"

end getYDATA

to pointToComma(aStr)

set theOffset to offset of "." in aStr

set theInteger to text 1 thru (theOffset - 1) of aStr

set theDecimal to text (theOffset + 1) thru -1 of aStr

set aStr to theInteger & "," & theDecimal as number

end pointToComma

As you were posting this I switched to French region and find that this works (with pointToComma handler corrected). Thanks for pointing out the problem.

set quoteProperties to "l1"

set theSymbol to "USDAUD=X"

set {s, t, c} to {"Sheet 1", "Table 1", "B2"}

tell application "Numbers" to tell front document's sheet s's table t

set theRate to my getYDATA(theSymbol, quoteProperties)

set theRate to my pointToComma(theRate)

set cell c's value to theRate

end tell

to getYDATA(symb, qProp)

set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"

set symbStr to "s=" & symb

set propStr to "&f=" & qProp

do shell script "curl -s " & "'" & baseURL & symbStr & propStr & "'"

end getYDATA

to pointToComma(aStr)

set theOffset to offset of "." in aStr

set aStr to aStr's text 1 thru (theOffset - 1) & "," & aStr's text (theOffset + 1) thru -1

end pointToComma

SG

Hi All,

I just read through the above correspondence with reg. to feeding exchange rates into numbers. So far this has been very helpful for me, however I would like to add a few more functions to my spread sheet.

I am using numbers for my personal budget, which is keeping track of my expenses in swedish crona (SEK), EUR and AED (Dirhams). On the main output, the spread sheet reports in SEK. I have a separate sheet, where all expenses are inserted for each day in either SEK, EUR or AED depending on where I am. I am not updating the budget on a daily basis but max. twice a week. So far I have been hand typing the historic exchange data, say from the last week and I was wondering whether the script could be advanced to do this step for me, referring to a date?

Looking forward to receiving your feedback.

Thanks in advance.

Regards

David

You can shoot an exchange rate directly into a Numbers cell, where a spreadsheet formula could then access it elsewhere in the sheet.

The following is an AppleScript to do that, and which uses Ruby to request and return the exchange rate from Yahoo. The assumption of the AppleScript is that you tell it what cell you want the exchange rate dumped into, and that you already have the Numbers spreadsheet open.

The Ruby code was borrowed from Github with all attribution intended.

The request to Yahoo returns a CSV array as [[S, l1, d1, t1, b, a]] where just the last trade price is captured (e.g. [1]). You can reference these and other symbols here:

• s = symbol
• l1 = last trade price *
• d1 = last trade date
• t1 = last trade time
• b = bid price
• a = ask price

1. Launch your Script Editor (Launchpad : Other : Script Editor)
2. Copy and paste the following code into your Script Editor
   1. Click the hammer icon to compile it
   2. Click the adjust arrow button to run it
3. Save a copy of the script as (e.g. yahoo_exchg.applescript

   Script Editor : File menu : Save…

   1. Save As: yahoo_exchg
   2. Location: Documents
   3. File Format: Text (this will add .applescript to the Save As filename
   4. Hide Extension: Unchecked
   5. Save
4. Save another copy of rthe script as an application (e.g. yahooX)

   Option key + Script Editor : File menu : Save As…

   1. Save As: yahooX
   2. Location: Desktop
   3. File Format: Application (adds .app to yahooX)
   4. Hide Extension: Checked
   5. Save
5. Double-click the yahoox application on your Desktop to use.

AppleScript code

--

-- you can add to the list, and if you do not choose and click "OK", then you will

-- be prompted to enter the exchange string

property exchg_list : {"AUDUSD", "USDAUD", "EURUSD", "USDEUR", "GBPUSD", "USDGBP"}

set exchg_select to (choose from listexchg_listwith prompt ¬

"Select your Exchange Conversion" default items "" with empty selection allowed without multiple selections allowed)

if exchg_select is false then error number -128

if exchg_select is {} then

display dialog "Enter exchange symbols as fromto (e.g. AUDUSD)" default answer "" -- fallback if not in list

set new_exchg to text returned of result

if new_exchg is not {} and length of new_exchg = 6 then

set exchg_select to new_exchg

else

display alert "Need an exchange pair ... quitting." giving up after 10

return

end if

end if

set exchg_value to yahoo_exchange(exchg_select) as number

tell application "Numbers"

tell active sheet of front document

tell table 1

set myRange to range "A2:A2"

set value of cells of myRange to exchg_value

end tell

end tell

end tell

return

on yahoo_exchange(astring)

return do shell script "ruby <<'EOF' - " & astring & "

#!/usr/bin/ruby

# coding: utf-8

# Influenced by https://gist.github.com/captainpete/1639522

require 'csv'

exchg = ARGV.join ' '

# get Yahoo Exchange rate and return last trade price

class YahooExchange

# set the default

def self.fetch(code = 'AUDUSA')

acsv = %x(curl -q -s \"http://download.finance.yahoo.com/d/quotes.csv?s=#{code}=X&f=sl1d1t1ba&e=.csv\")

CSV.parse(acsv).flatten[1]

end

end

print YahooExchange.fetch(exchg)

EOF"

end yahoo_exchange

Here's as simple script that places the current rate in a designated cell in a specified sheet and table of the front document.

1. Copy-paste script below into into AppleScript Editor (in Applications > Utilities)
2. Make sure you have your document set up in way that the script can address it properly. Change the sheet, table, and cell names in the script (where currently there is "Sheet 1", "Table 1", "B2") to match your document.
3. Click the triangle 'run' button.

This can be made more sophisticated, e.g. retrieve the quote data and time, etc., or retrieve several different exchange rates at once. It can also be placed in the menu and/or attached to a keyboard shortcut.

But it should get you started. Post if you encounter problems.

SG

-- properties that could be used: s symbol; l1 current rate; d1 date; t1 time; b bid; a asked

-- see http://stackoverflow.com/questions/181990/programmatically-access-currency-excha nge-rates

set quoteProperties to "l1"

set theSymbol to "USDAUD=X"

set {s, t, c} to {"Sheet 1", "Table 1", "B2"}

tell application "Numbers" to tell front document's sheet s's table t

set cellc'svalue to my getYDATA(theSymbol, quoteProperties)

end tell

to getYDATA(symb, qProp)

set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"

set symbStr to "s=" & symb

set propStr to "&f=" & qProp

do shell script "curl -s " & "'" & baseURL & symbStr & propStr & "'"

end getYDATA

As currently written, either script has the following criteria:

1. The recipient Numbers spreadsheet is already open, and frontmost (in case of multiple open spreadsheets) in Numbers v3+. The scripts are unconcerned with document demographics.
2. The spreadsheet is unlocked.
3. The designated cell in the script is the one where you want the last trade price written.
4. Yahoo has not changed their Finance API, and the URL continues to work.

Hi Graeme,

Thanks for the feedback, and welcome to AppleScript! Just to confirm: as written the scripts look for the currently open document that is at the front and will not target closed documents or other documents that may happen to be open and have similar or same sheet and table names. Doing it that ways simplifies the coding and covers most use cases. It is possible, however, to have AppleScript open a Numbers document in a particular location on your Mac.

SG

Grahdy wrote:

is that data streaming from Yahoo Finance into the designated Sheet, Table and Cell?

I wouldn't say it is "streaming." The data is updated each time the script is run. So you have to click the 'run' button or (if you've put the script in the menu or in what's called an Automator Service) make a menu choice or hit a keyboard shortcut. You could also make the script run automatically every few seconds or so, but not sure what Yahoo's policy is on number of times you access the API. In general I would think of this as a substitute for real-time data streaming. It's merely a convenient way to retrieve values to update your document.

SG

Thanks a lot! Only thing is that it now appears like text, and I cannot calculate with it. Can it also be appearing with a komma and as a value? 😀

It sounds as if your region settings have , instead of . as the decimal separator. If so, the following script may work for you.

SG

set quoteProperties to "l1"

set theSymbol to "USDAUD=X"

set {s, t, c} to {"Sheet 1", "Table 1", "B2"}

tell application "Numbers" to tell front document's sheet s's table t

set theRate to my getYDATA(theSymbol, quoteProperties)

set theRate to my pointToComma(theRate)

set cell c's value to theRate

end tell

to getYDATA(symb, qProp)

set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"

set symbStr to "s=" & symb

set propStr to "&f=" & qProp

do shell script "curl -s " & "'" & baseURL & symbStr & propStr & "'"

end getYDATA

to pointToComma(aStr)

set theOffset to offset of "." in aStr

set aStr to (aStr's text 1 thru theOffset) - 1 & "," & aStr's text (theOffset + 1) thru -1 as string

end pointToComma