When you plan your projects with Merlin for Mac, you will find lots of autocalculating columns helping you in accomplishing your goal.

Take the ‘Expected costs‘ column for example. It calculates expected costs by adding automatically expected work costs (due to assignments and resource prices), element costs and  base costs.

The project’s currency is a project setting and can be found or changed in File > Project Settings > General > Financial > Currency Symbol.

Should you need the same costs in an additional currency, you may enable ‘Expected Costs’ in the outline, copy the rows, paste in Excel, Numbers or a similar party application, and let the third party application calculate the costs in a second currency according to the current exchange rate.

However… to those visiting this pages from time to time should not come as a surprise,  that we’ve wrote a script accomplishing this in Merlin. Feel free to use and share as find appropriate 🙂

(*     Scripting with Merlin 2

    You may incorporate this ProjectWizards sample code into your program(s) without
    restriction.  This ProjectWizards sample code has been provided "AS IS" and the
    responsibility for its operation is yours.  You are not permitted to
    redistribute this ProjectWizards sample code as "ProjectWizards sample code" after having
    made changes.  If you're going to redistribute the code, we require
    that you make it clear that the code was descended from ProjectWizards sample
    code, but that you've made changes.

    Copyright ® 2013 ProjectWizards, Melle, Germany. All rights reserved.

    This script goes throught all activities of the top most opened project, asks for the project currency, the foreign currency, the exchange rate (proposes a link of a service outputting this information), calculates expected costs based on expected work, element and base costs of each item in the foreign currency, and writes this value in the 'Additional title' column. 

     Applescript restrictions allow this script neither to retrieve for the project's currency, nor define a custom column title to 'Additional title', or define a right alignment of the same column. Those actions are to be done by the Merlin user in the respective dialogues.

    Written by Vicky Stamatopoulou
    For ProjectWizards
    Sept 4, 2013

    Copyright © for the 'Trim' function belongs to the late Jürgen Schell · http://www.j-schell.de

*)

property NeedAProjectMessage : "You must have an open project in order to run this script"

property BaseCurrencyTitle : "Base currency"
property BaseCurrency : "Please select your project's base currency."
property DefaultCurrencyString : "USD"

property ForeignCurrencyTitle : "Foreign currency"
property ForeignCurrency : "In which foreign currency should expected costs get calculated?"
property DefaultForeignCurrencyString : "EUR"

property GetExchanceRateTitle : "Exchange rate"
property GetExchanceRateFrom : "Get exchange value (for example from following link) and replace the highlighted text; for example 0.458"

property ErrorMessage : "No nummmerical value found. Restart the script and enter a valid nummerical value as exchange rate; for example 0.458"

property DoneTitle : "Calculation completed"
property DoneMessage : "Make sure you rename the column 'Additional Title' to show the currency of the calculated expected costs; for example 'Expected Costs in "

property CustomColumnTitles : "

Note: Custom titles can be defined over View > Show View Options > Columns > Custom title
"

global inList, myVar, myCurrencyList

set myCurrencyList to {"USD", "AUD", "CAD", "CHF", "EUR", "GBP", "Other"}

on collectAllSubactivities(parentActivity)
    global inList
    tell application "Merlin"
        set inList to inList & (parentActivity as list)

        repeat with act in activities of parentActivity
            collectAllSubactivities(act, inList) of me
        end repeat

    end tell
end collectAllSubactivities

on allSubActivities(parentActivity)
    global TheDueDate
    set inList to {}
    tell application "Merlin"
        collectAllSubactivities(parentActivity, inList) of me
    end tell
    return inList
end allSubActivities

on trim(strg)
    -- Copyright © for the 'Trim' function belongs to the late Jürgen Schell · http://www.j-schell.de
    -- http://www.j-schell.de/book/export/html/606
    ignoring white space
        -- do the left trim
        set left_counter to 1
        repeat with J from 1 to length of strg
            if " " = (character left_counter of strg) then
                set left_counter to left_counter + 1
            else
                exit repeat
            end if
        end repeat
        try
            set strg to text left_counter through -1 of strg
        on error
            set strg to ""
        end try
        -- end left trim

        -- do the right trim
        set right_counter to -1
        repeat with J from 1 to length of strg
            if " " = (character right_counter of strg) then
                set right_counter to right_counter - 1
            else
                exit repeat
            end if
        end repeat
        try
            set strg to text 1 through right_counter of strg
        on error
            set strg to ""
        end try
        -- end right trim
    end ignoring
    return strg
end trim

tell application "Merlin"
    global inList
    try
        set doc to the first document
    on error
        display dialog NeedAProjectMessage
        set chosenFile to (choose file)
        open chosenFile
        set doc to the first document
    end try

    set proj to root project of doc

    set TheBaseCurrency to choose from list myCurrencyList with title BaseCurrencyTitle with prompt BaseCurrency default items DefaultCurrencyString
    try
        set TheBaseCurrency to first item of TheBaseCurrency as string

        set TheForeignCurrency to choose from list myCurrencyList with title ForeignCurrencyTitle with prompt ForeignCurrency default items DefaultForeignCurrencyString
        set TheForeignCurrency to first item of TheForeignCurrency as string

        set TheUrl to "http://www.x-rates.com/calculator/?from=" & TheBaseCurrency & "&to=" & TheForeignCurrency & "&amount=1.00"

        set GetTheRateValueDialogue to display dialog GetExchanceRateFrom with title GetExchanceRateTitle default answer TheUrl
        set TheCambioRate to trim((text returned of GetTheRateValueDialogue) as text) of me

        # remove currency string
        set TheCambioRate to (text 1 through ((offset of TheForeignCurrency in TheCambioRate) - 1) of TheCambioRate)
        set TheCambioRate to trim(TheCambioRate) of me

        try
            set TheCambioRate to TheCambioRate as number

            set allActivities to allSubActivities(proj) of me

            repeat with act in allActivities

                tell act
                    set subtitle2 to ""
                    try
                        set myVar to 0
                        if planned work cost is not missing value then set myVar to planned work cost

                        if elements cost deep is not missing value then set myVar to myVar + elements cost deep
                        if base cost deep is not missing value then set myVar to myVar + base cost deep

                        set myVar to (myVar * TheCambioRate)
                        if myVar > 0 then
                            tell me to set myVar to (round (myVar * 100) rounding as taught in school) / 100
                            set subtitle2 to myVar
                        end if
                    end try
                end tell

            end repeat
            display dialog DoneMessage & TheForeignCurrency & "'" & CustomColumnTitles with title DoneTitle buttons {"Ok"}
        on error
            display dialog ErrorMessage
        end try
    on error

    end try

end tell