Grahdy

Q: Feeding Exchange Rate into Numbers

Hi,

 

I'm creating a Position Size Calculator in Numbers to manage my risk in the trading game. Most trading instruments are denominated in foreign currencies and to work out what I'm risking in Australian dollars, I need exchange rate feed into numbers to calculate from. Most of the time it would be calculating the exchange rate between Australia and United States. Otherwise I would have to manually input the exchange rate.

 

Is it possible to do this, if so how? I'm guessing a site like Yahoo Finance would be used!

 

Thanks in advance ...

Graeme

iMac (21.5-inch, Late 2013), OS X Yosemite (10.10.1)

Posted on Jul 16, 2016 9:40 AM

Close

Q: Feeding Exchange Rate into Numbers

  • All replies
  • Helpful answers

  • by VikingOSX,

    VikingOSX VikingOSX Jul 16, 2016 4:57 PM in response to Grahdy
    Level 7 (20,849 points)
    Mac OS X
    Jul 16, 2016 4:57 PM in response to Grahdy

    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
      1. 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)
      1. 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 list exchg_list with 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

  • by SGIII,Solvedanswer

    SGIII SGIII Jul 16, 2016 11:08 PM in response to Grahdy
    Level 6 (10,681 points)
    Mac OS X
    Jul 16, 2016 11:08 PM in response to Grahdy

    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 cell c's value 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

  • by Grahdy,

    Grahdy Grahdy Jul 16, 2016 11:55 PM in response to SGIII
    Level 1 (4 points)
    iWork
    Jul 16, 2016 11:55 PM in response to SGIII

    It worked!! You guys are amazing!! Best Forum hands down! I didn't even know of or heard of Apple Script Editor until last night.

     

    Just some questions though to get my head around this ...

     

    I closed all Numbers spreadsheets and generated a new document, then I entered the script, what would happen if I had multiple spreadsheets open that had the same parameters("Sheet 1", "Table 1", "B2")? I'm guessing referencing to your words from your post "front document" it would only attempt to input into the front document!

     

    Next question, if I open a new spreadsheet, enter the script, save the spreadsheet, move it around etc, would the script always find that designated spreadsheet, sheet number, table number and cell?

     

    Thanks again for your help

    Graeme  

  • by VikingOSX,

    VikingOSX VikingOSX Jul 17, 2016 4:21 AM in response to Grahdy
    Level 7 (20,849 points)
    Mac OS X
    Jul 17, 2016 4:21 AM in response to Grahdy

    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.
  • by SGIII,

    SGIII SGIII Jul 17, 2016 4:01 PM in response to Grahdy
    Level 6 (10,681 points)
    Mac OS X
    Jul 17, 2016 4:01 PM in response to Grahdy

    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

  • by Grahdy,

    Grahdy Grahdy Jul 18, 2016 1:39 AM in response to SGIII
    Level 1 (4 points)
    iWork
    Jul 18, 2016 1:39 AM in response to SGIII

    Ok I understand!

     

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

     

    Thanks

    Graeme

  • by SGIII,

    SGIII SGIII Jul 18, 2016 5:27 AM in response to Grahdy
    Level 6 (10,681 points)
    Mac OS X
    Jul 18, 2016 5:27 AM in response to Grahdy

    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

  • by Grahdy,

    Grahdy Grahdy Jul 18, 2016 6:14 AM in response to SGIII
    Level 1 (4 points)
    iWork
    Jul 18, 2016 6:14 AM in response to SGIII

    Hi SG,

     

    Because I trade about 20 different markets I need it to run automatically. There would be no point finding the script and hit the run button every time, sort of defeats the whole purpose.

     

    It would be great if Apple Script could be coded to hit the run button automatically. I just read Yahoo's API Terms of Use and I'm certainly not compromising any rules other than maybe this one ...

     

    1.71. YOU SHALL NOT

     

    'Use the Yahoo APIs in a manner that exceeds reasonable request volume, constitutes excessive or abusive usage.'

     

    Taking into account if the script runs every say 5 minutes and its seeking prices of only 20 markets(pairs), I can't imagine that being a violation of use at all.

     

    Thanks for your time.

    Graeme

  • by paulrosmalen,

    paulrosmalen paulrosmalen Jul 26, 2016 2:15 PM in response to SGIII
    Level 1 (4 points)
    iWork
    Jul 26, 2016 2:15 PM in response to SGIII

    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?

  • by SGIII,

    SGIII SGIII Jul 27, 2016 7:13 AM in response to paulrosmalen
    Level 6 (10,681 points)
    Mac OS X
    Jul 27, 2016 7:13 AM in response to paulrosmalen

    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