9 Replies Latest reply on Aug 29, 2012 9:29 AM by comment

    Currency conversions

    danielle-gwa

      Currently we have a database containing wind turbine part details. Within wach part record there may be a price field. Dependant on where we buy the part there will of course be a range of currencies that the price may be in (e.g. Euros, Pounds sterling, US dollars, Danish Krone, etc).

       

      I want to create a report of all the parts, but show an overall cost for the parts in one currency, Euros. How would i go about showing what all the part prices are in Euros??

       

       

       

      Maybe i need to create a calculation field that says

      If in Pounds sterling, divide by 0.7918 and multiply by 1

       

      If in Euros, do nothing

       

      If in US Dollars, divide by 1.2557 and multiply by 1

       

       

      Then i would need to keep manually adjusting the conversion rate.

       

       

       

       

       

      Can someone please advise me on the best way to do this if different from my suggestion????

       

      Thank you

        • 1. Re: Currency conversions
          comment

          I suggest you add a table of CurrencyRates, with fields for CurrencyCode and Rate. Then define a relationship between the Parts table and CurrencyRates, matching on CurrencyCode. Finally, add a calculation field to the Parts table =

           

          Price / CurrencyRates::Rate

          • 2. Re: Currency conversions

            Hi Danielle,

             

            In addition to the advice you've had from Michael, you'll likely need a way of updating the exchange rates as they are continually drifting over time.

             

            I've addressed this problem for our own business by having a separate FMP file that accesses foreign exchange rates that are published on the web. The file has a web viewer that accesses the site, then we parse out the data that we need and save it in a record alongside the date of the published exchage rate. You would then use the date for the foreign currency transaction that paid each invoice as a second term in the relationship that Michael proposed.

             

            This maybe overkill for your needs but I can let you have a copy of our file for $NZ:$US if you need an example to work from.

             

            Sincerely,

             

            John

            • 3. Re: Currency conversions
              mbraendle

              Although the idea of a CurrencyRates table is right, I wouldn't directly use the rate of the related table to calculate the price, but copy the value of the exchange rate via lookup to a exchange rate field in the parts table and then use this field to calculate the price.

               

              This way you make sure that you don't change all prices of your parts a posteriori when you change an exchange rate in the Currency Rates table; because you want to have the price of the part fixed at the time when you bought it.

              • 4. Re: Currency conversions
                danielle-gwa

                Thank you Michael Horak for your reply.

                 

                Coulf you provide me with any more details on how to do that?

                Do you have any examples i could see at all, that would be helpful?

                 

                So if i create a new table in manage database.

                Add one field called CurencyCode and populate the records with Euros, Pounds sterling, US dollars, etc

                And then add a second field for the rate (e.g. the Euros rate is just 1, pounds sterling rate currently is 0.7918 pounds to the Euro, Us dollars rate currently is 1.2557 dollars to the Euro, etc)

                 

                Then in manage database relationships, i match a relationship between parts table anf crrency rates- Could you explain the details of this part a bit clearer for me as i dont quite understand what fields i create a relationship between.??

                 

                Thank you

                • 5. Re: Currency conversions
                  danielle-gwa

                  Hi John

                   

                  Thank you also for your help with my problem.That sounds like a complex but a great solution to the exchange rate problem.

                   

                  I would greatly appreciate a copy of the file for currency exchange rates if you are willing to help me ?? Let me know if you can provide it for me?

                   

                  Thank you kindly

                   

                  Danielle

                  • 6. Re: Currency conversions
                    comment

                    MartinBraendle wrote:


                    you want to have the price of the part fixed at the time when you bought it.

                     

                    Well, that depends on whether you're buying or selling... The way I understand this, the price (as opposed to cost) is supposed to change with the rate. Of course, when actually selling the part, the price needs to be looked up into the record of the sale.

                    • 7. Re: Currency conversions
                      mbraendle

                      I think it's pretty much clear from Danielle's explanation: She wants to calculate the total cost of the parts that were bought at a given time with the currency rate at that time. So it needs to be stored with the part.

                      • 8. Re: Currency conversions
                        RubenVanDenBoogaard

                        Hi Danielle,

                         

                        Herewith a small sample file that shows how you can use the (free) 360Works Scriptmaster

                        plugin to get the data from Yahoo

                         

                        The 360Works scriptmaster can be downloaden from:

                         

                        http://360works.com/scriptmaster/

                         

                        and it will grab the data much faster then using a webviewer.

                         

                        If you go to Yahoo and search for  example the Euro USD rate you get the following page

                         

                        http://finance.yahoo.com/q?s=EURUSD=X

                         

                        Under Toolbox you will find 'Download data delayed'  if you use that link:

                         

                        http://download.finance.yahoo.com/d/quotes.csv?s=EURUSD=X&f=sl1d1t1c1ohgv&e=.csv

                         

                        then you will only het the data, like : "EURUSD=X",1.2539,"8/29/2012","11:59am",N/A,N/A,N/A,N/A,N/A

                         

                        which can be easily pasted out in the table.

                         

                        Hope that helps!

                         

                        Best regards,

                         

                        Ruben van den Boogaard

                        Infomatics Software

                        ruben@infomatics.nl

                        • 9. Re: Currency conversions
                          comment

                          danielle-gwa wrote:

                           

                          Coulf you provide me with any more details on how to do that?

                           

                          There is not much to add; your understanding is correct. I am attaching a rough example file. Of course, if Martin is correct - as he very well could be - then you need to adjust the file so that the rate is looked up into the Products table at the time a new product record is created.

                           

                          As others have noted, you can update the rates by importing from a web source - though I am not sure a plugin is required. For example, the European Central Bank provides exchange rates in XML format, which Filemaker can import directly, given a suitable XSLT stylesheet.