MichaelJarjour

Q: Currency Conversion in Numbers

I'm pretty new to numbers, so forgive me if this is a dumb question.

 

I'm trying to convert a dollar amount in one cell to another currency in another cell. Preferrably with live exchange rates. Is there a way to do this?

MacBook Pro, OS X Mavericks (10.9.3)

Posted on Jun 11, 2014 5:26 PM

Close

Q: Currency Conversion in Numbers

  • All replies
  • Helpful answers

  • by Jerrold Green1,Helpful

    Jerrold Green1 Jerrold Green1 Jun 11, 2014 5:53 PM in response to MichaelJarjour
    Level 7 (29,996 points)
    Jun 11, 2014 5:53 PM in response to MichaelJarjour

    Michael,

     

    The conversion is easily done in Numbers, but the "Live" part would require some scripting; Automator and Applescript. I don't want to discourage you, but I'll just say that if it was me, I'd Copy and Paste from a Desktop Widget. If you have your heart set on this concept, there are examples in the history of these discussions and there are current helpers that will assist you with the scripting.

     

    Jerry

  • by MichaelJarjour,

    MichaelJarjour MichaelJarjour Jun 11, 2014 5:55 PM in response to Jerrold Green1
    Level 1 (1 points)
    Jun 11, 2014 5:55 PM in response to Jerrold Green1

    The "easy" version sounds more intriguing. How does that work?

  • by Jerrold Green1,

    Jerrold Green1 Jerrold Green1 Jun 11, 2014 6:11 PM in response to MichaelJarjour
    Level 7 (29,996 points)
    Jun 11, 2014 6:11 PM in response to MichaelJarjour

    Give us an example of the conversion you want to do and how you want it to appear in the document.

     

    Jerry

     

    Edit: While I am at it, I'll give you the KB instructions for creating a Web Clipping in Dashboard.

     

    To make this work, Create the Web Clip Widget for the currency rate website you prefer. With your Numbers document open, switch to the Dashboard, Select and Copy the rate, switch back to Numbers and Paste the rate into your document.

     

    J.

  • by MichaelJarjour,

    MichaelJarjour MichaelJarjour Jun 11, 2014 6:22 PM in response to Jerrold Green1
    Level 1 (1 points)
    Jun 11, 2014 6:22 PM in response to Jerrold Green1

    I have a spreadsheet with a bunch of amounts in Euros, the sum of which I want to have displayed in Euros and in CHF.

  • by Jerrold Green1,Solvedanswer

    Jerrold Green1 Jerrold Green1 Jun 11, 2014 6:58 PM in response to MichaelJarjour
    Level 7 (29,996 points)
    Jun 11, 2014 6:58 PM in response to MichaelJarjour

    Add a column for CHF, and in the Header Paste the conversion figure. Let's say your Euros are in Column D; then your CHF calculation, assuming they will be in Column E, would be:

     

    =E$1*D

     

    or

     

    =E$1/D

     

    depending on which way you get the conversion from the website.

     

    In a Footer row, write:

     

    =SUM(D)

     

    in Column D and Copy/Paste to the Footer cell of Column E.

     

    Jerry

  • by SGIII,Helpful

    SGIII SGIII Jun 11, 2014 7:28 PM in response to Jerrold Green1
    Level 6 (10,637 points)
    Mac OS X
    Jun 11, 2014 7:28 PM in response to Jerrold Green1

    Here's a way to automate this a little (one click updates the rate):

     

    Screen Shot 2014-06-11 at 10.23.49 PM.png

     

     

    The script below will place the Yahoo! Finance rate in the target cell here C1.

     

    Copy into AppleScript Editor, click somewhere in table, click green triangle "Run" button.

     

    SG

     

     

    --click anywhere in table, run; places exchange rate in target cell

    property targetCell : "C1"

    property symbol : "eurchf=x" --> Swiss Francs per Euro

    set sourceText to do shell script "curl -s http://finance.yahoo.com/q?s=" & symbol

    set extractedText to extractBetween(sourceText, "yfs_a00_" & symbol & "\">", "</span>")

    return placeValueInCell(extractedText, targetCell)

     

    --handlers:

    to placeValueInCell(theValue, targetCell)

              try

                        tell application "Numbers" to tell front document to tell active sheet to tell ¬

                                  (first table whose class of selection range is range) to set ¬

                                  value of cell targetCell to theValue

              on error

                        display dialog "Did you select a table?" buttons "Cancel" default button "Cancel"

              end try

    end placeValueInCell

     

    to extractBetween(sourceText, startText, endText)

              set {oTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, startText}

              set endItems to text of text item -1 of sourceText -- everything after startText

              set AppleScript's text item delimiters to endText -- get ready to split endText

              set textInBetween to text of text item 1 of endItems -- take everything before endText

              set AppleScript's text item delimiters to oTID -- restore original values

              return textInBetween

    end extractBetween

    --end of script

  • by SaltSeaShark,

    SaltSeaShark SaltSeaShark Nov 23, 2014 2:20 AM in response to SGIII
    Level 1 (18 points)
    Mac OS X
    Nov 23, 2014 2:20 AM in response to SGIII

    SGIII,

     

    Thanks for workaround.

     

    Still immensely disappointing that Apple haven't provided a Numbers Function since the code already exists in Calculator.

     

    Really annoying, and, sadly, what we've come to expect from Apple these days.

  • by paulrosmalen,

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

    Dear SGIII,

     

    Thank you very much.
    I tried it, but I got this message:

     

    Found. Redirecting to http://finance.yahoo.com/quote/eurchf=x?ltr=1

     

    Is there a way to get a number?

    And do you maybe have an idea to check usd vs euro?

     

    Thanks a lot for helping out!

     

    Kind regards,

     

    Paul

  • by VikingOSX,

    VikingOSX VikingOSX Jul 26, 2016 4:48 AM in response to paulrosmalen
    Level 7 (20,613 points)
    Mac OS X
    Jul 26, 2016 4:48 AM in response to paulrosmalen

    Look at this 2016 post from SGIII, as it incorporates Yahoo URL changes that were not present in 2014.