Exchange Rate Table for query

[That_Guy]

I have been tasked with having a column in a SahrePoint Site to convert Local Currency into USD. I attempted doing this by using SharePoint Calculated fields, but appear to have exceeded the number of characters or statement allowed in a Calculated Field.



I am thinking, that Access would be a better way to do this but I am not sure how to go about creating this in Access. This is what I had built in SharePoint. Currency indicates a field in SharePoint that is populated with currency abbreviations. Total Cost (Local Currency) is a field in SharePoint that is calculated using other fields in SharePoint. The number below, in blue the code, indicates the exchange rate.

=IF(Currency="USD",[Total Cost (Local Currency)],SUM(IF(Currency="ARS",[Total Cost (Local Currency)]* 0.212268 ),SUM(IF(Currency="BRL",[Total Cost (Local Currency)]*0.491302)),SUM(IF(Currency="GBP",[Total Cost (Local Currency)]*1.598921))),SUM(IF(Currency="CNY",[Total Cost (Local C urrency)]*0.158994)))),

Ultimately what I would like to build in Access is something that pulls the exchange rate from a table in lieu of me entering in the exchange rate manually each time I need to update the SharePoint Site. I have created an Exchange Rate Table that has the following type of Data (this is a very tiny sample).

Formal Name	Exchange Rate	Currency
Argentine Peso	0.212268	ARS
British Pound	1.598921	GBP

I need the calculation to occur and update tables (WO Extensions and WO Extension Master) that are linked to SP. Any assistance you can provide would be greatly appreciated. Thanks!

[June7]

Can you join the Exchange table to the other table(s)? Can you use DLookup in query?

I don't have experience with Sharepoint.

[rpeare]

Are you just using this as a calculator or do you actually need to store the results of your conversions (i.e. it's related to a specific customer on a specific day, etc.)

If it's just a calculator that's pretty simple.

Conversion.zip

[orange]

Exchange rates can/do vary. I have sample database( mdb) that uses vba and gets exchange rates from an internet site (the European Central Bank).

+---------------------------------------------------------------------------------------------------------------+
NOTE: After posting this I realize that my source (the European Central Bank) does NOT include ARS (Argentine Peso). But I did find an online site that has many currencies. see http://rss.timegenie.com/foreign_exchange_rates_forex
+---------------------------------------------------------------------------------------------------------------+

Here is my code

 
'---------------------------------------------------------------------------------------
' Procedure : LeeExchangeRate
' Author    : Jack
' Date      : 27-06-2012
' Purpose   : To get the daily exchange rates from the European Central Bank
'  at  http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
' These currency/rate pairs show the value of 1 euro in this related currency on this date
'
' From a learning view, this is sending an XMLHTTP request and getting a Response and
' using the DOMDocument amd related objects to extract the required data
' from the xml response. This approach seems cleaner and more consistent than using
' vba functions to search and parse strings within the response.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency:
' **********This requires a reference to Microsoft XML, v6.0 **************
'--------------------------------------------------------------------------
'
Sub LeeExchangeRate()

    Dim arr(32, 1) As String    'array to hold Currency and Rate


    Dim Resp As New DOMDocument
    Dim Req As New XMLHTTP
    Dim objNodeList As IXMLDOMNodeList
    Dim objNode As IXMLDOMNode
    Dim objAttribute As IXMLDOMAttribute
    Dim mCurrency As String
    Dim mRate As String
    Dim mTime As String
    Dim mList As String
    Dim FromValue As String
    Dim ToValue As String
    Dim AmtQty As Double
    Dim x As Integer
    Dim i As Integer
    On Error GoTo LeeExchangeRate_Error

    Req.Open "GET", "http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml", False
    Req.send

    While Req.Status <> 200
        Debug.Print "waiting for response*** info"
        DoEvents
    Wend

    Resp.loadXML Req.responseText

    ' uncomment the next line to see the response
    ' Debug.Print "XML Response is " & vbCrLf & "~~~~~~~~~~~~~~~~~" & vbCrLf & Req.responseText

    ' x = Resp.getElementsByTagName("Cube").Length 'how many  Cube nodes
    ' Debug.Print "The number of <Cube> nodes is   " & x

    '
    '  Get all Cube nodes.
    '

    Set objNodeList = Resp.getElementsByTagName("Cube")

    '
    '  For each cube node if it has attributes currency and rate then display the values.
    ' One Cube row has no attributes and one has only a time attribute.
    '

    For Each objNode In objNodeList
        If (objNode.Attributes.Length > 0) Then
            ' Get the "time" attribute for the date to which these exchange rates apply
            Set objAttribute = objNode.Attributes.getNamedItem("time")
            If (Not objAttribute Is Nothing) Then
                Debug.Print "Exchange Rates from European Central Bank   as at  " & objAttribute.text & vbCrLf _
                            & vbCrLf & "**Read these as 1 euro = **" & vbCrLf
                mTime = objAttribute.text
            End If
            ' Get the "currency" attribute
            Set objAttribute = objNode.Attributes.getNamedItem("currency")
            If (Not objAttribute Is Nothing) Then
                mCurrency = objAttribute.text
                arr(i, 0) = mCurrency

            End If
            ' Get the associated "rate" attribute
            Set objAttribute = objNode.Attributes.getNamedItem("rate")
            If (Not objAttribute Is Nothing) Then
                mRate = objAttribute.text
                arr(i, 1) = mRate
                i = i + 1
            End If
            'Put the data in my variables for display
            If mCurrency > " " And mRate > " " Then
                Debug.Print mTime & "   " & mCurrency & "  " & mRate
            End If
        End If
    Next objNode

    '
    '**Routine to create values for the array of Currency Names
    '**run thus one time to populate the values
    For i = LBound(arr) To UBound(arr)
        If arr(i, 0) = Forms!form17.ComboFrom Then
            FromValue = arr(i, 1)
            Exit For
        End If
    Next i
    For i = LBound(arr) To UBound(arr)
        If arr(i, 0) = Forms!form17.ComboTo Then
            ToValue = arr(i, 1)
            Exit For
        End If
    Next i

    AmtQty = CDbl(Forms!form17.Text4)
    'MsgBox AmtQty & " " & Forms!form17.ComboFrom & " equals " & (CDbl(ToValue) / CDbl(FromValue)) * CDbl(AmtQty) & "  " & Forms!form17.ComboTo

    Forms!form17.Text9 = (CDbl(ToValue) / CDbl(FromValue)) * CDbl(AmtQty)
    Forms!form17.Text9.Requery
    Forms!form17.Text9.Visible = True

    On Error GoTo 0
    Exit Sub
DisplayArray:


LeeExchangeRate_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure LeeExchangeRate of Module xmlhttp_etc"

End Sub

[rpeare]

haha orange I always do examples for things that I find interesting looks like you do the same

I thought about what you did but I figured they'd have a percentage over 'prime' to account for fees involved in the exchange now there's tons to play with!

[orange]

rpeare,
Some code for you based on the free xml exchange rates at
http://rss.timegenie.com/foreign_exchange_rates_forex

'---------------------------------------------------------------------------------------
' Procedure : TimegenieExchangeRate
' Author    : Jack
' Date      : 11-10-2012
' Purpose   : To get a list of Exchange rates from rss.timegenie.com/forex.xml
'This site provides free exchange rate info for several  ~75 currencies daily.
' It does not identify the Date of the rate in the xml file???
'
'I have used my LeeExchangeRate proc that used the European central banks rates as a template.
'I have modified that code base to this as  separate exercise.

'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub TimegenieExchangeRate()

          Dim arr(100, 2) As String    'array to store the Code/description and rate for the currencies
          Dim Resp As New DOMDocument
          Dim Req As New XMLHTTP
          Dim objNodeList As IXMLDOMNodeList
          Dim objNode As IXMLDOMNode
          Dim objAttribute As IXMLDOMAttribute
          Dim mCurrency As String
          Dim mRate As String
          Dim mTime As String
          Dim mDescription As String
          Dim x As Integer
          Dim i As Integer


10        On Error GoTo TimegenieExchangeRate_Error

20        Req.Open "Get", "http://rss.timegenie.com/forex2.xml"
30        Req.send

40        While Req.Status <> 200
50            Debug.Print "waiting for response*** info"
60            DoEvents
70        Wend

80        Resp.loadXML Req.responseText

          ' uncomment the next line to see the response
          'Debug.Print "XML Response is " & vbCrLf & "~~~~~~~~~~~~~~~~~" & vbCrLf & Req.responseText

          
90        Debug.Print "Exchange Rates from TimeGenie   as at  " & Date  _
                      & vbCrLf & "**Read these as 1 euro = **" & vbCrLf
100       Set objNodeList = Resp.getElementsByTagName("currency")

110       For Each objNode In objNodeList
120           If (objNode.Attributes.Length > 0) Then
                  '
                 
                  ' Get the "code" attribute
130               Set objAttribute = objNode.Attributes.getNamedItem("code")
140               If (Not objAttribute Is Nothing) Then
150                   mCurrency = objAttribute.text
160                   arr(i, 0) = mCurrency
170               End If

                  ' Get the "description" attribute
180               Set objAttribute = objNode.Attributes.getNamedItem("description")
190               If (Not objAttribute Is Nothing) Then
200                   mDescription = objAttribute.text
210                   arr(i, 1) = mDescription
220               End If

                  ' Get the associated "rate" attribute
230               Set objAttribute = objNode.Attributes.getNamedItem("rate")
240               If (Not objAttribute Is Nothing) Then
250                   mRate = objAttribute.text
260                   arr(i, 2) = mRate
270                   i = i + 1
280               End If

                  'Put the data in my variables for display
290               If mCurrency > " " And mRate > " " Then
300                   Debug.Print mCurrency & "  " & mDescription & "   " & mRate
310               End If
320           End If
330       Next objNode
340       Debug.Print "filled array"
350       On Error GoTo 0
360       Exit Sub

TimegenieExchangeRate_Error:

370       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure TimegenieExchangeRate of Module xmlhttp_Etc"

End Sub

Note To: That_guy - You may be able to automate your daily exchange rate with some code getting the exchange rate from the internet.

[That_Guy]

Wow! Thanks for the excellent responses. I just wish I had the know how on how to implement them. I can't download the database from 4share, it is a blocked site on my machine. Ultimately I would like the exchange rates to update themselves, but I have no idea how to do that either. I need to take some access classes. In the meantime is there a way to make this super simple by putting similar code to what I originally published to be calculated in Access using IIF? I keep getting Syntax Errors when I try something like this in the Field row.

Total Cost USD: =IIF(Currency="USD",[Total Cost (Local Currency)],SUM(IF(Currency="ARS",[Total Cost (Local Currency)]* 0.212268 ),SUM(IF(Currency="BRL",[Total Cost (Local Currency)]*0.491302)),SUM(IF(Currency="GBP",[Total Cost (Local Currency)]*1.598921))),SUM(IF(Currency="CNY",[Total Cost (Local Currency)]*0.158994))))

Any idea why I am getting Syntax Errors?

I do not get errors when I used this code in a SharePoint Calculation. I need to get this up and running, even if it is primitive. After I get it just to do the basic calculation I will review the information you guys provided on making it possibly update automatically.... which would be awesome.

[orange]

I don't use Sharepoint. Perhaps others here could comment/assist.

You could copy the procedure in the latest post and paste it into a module in your Access Test database (or create a test access db just for this). You will need to have access to the internet( and it seems your network/IT people may have shut that down generally).

You need a reference to Microsoft XML, v6.0
' **********This requires a reference to Microsoft XML, v6.0 **************

The code I gave extracts data from Timegenie into an array. You could put the data into a table, and refresh it regularly.
You would use that table in your query and select the appropriate currency and rate for calculations.

Perhaps there is a simple way to do this from Sharepoint, but I have never used Sharepoint.

[That_Guy]

Let's go ahead and take the SharePoint element out of the equation. Is it possible to have Access make the calculation simply by manually entering in the exchange rate? Why won't Access accept the code in the "Field" area?

Something like this in Design View:

Field:	Total Cost (Local Currency)	Total Cost (USD): =IIF(Currency="USD",[Total Cost (Local Currency)],SUM(IF(Currency="ARS",[Total Cost (Local Currency)]* 0.212268 ),SUM(IF(Currency="BRL",[Total Cost (Local Currency)]*0.491302)),SUM(IF(Currency="GBP",[Total Cost (Local Currency)]*1.598921))),SUM(IF(Currency="CNY",[Total Cost (Local Currency)]*0.158994))))	Currency
Table:	Extensions	Extensions	Extensions
Sort:
Show:	Checked	Checked	Checked
Criteria:

[orange]

Please describe your situation more. What are your tables?
What exactly are you trying to do?
Where did you find the syntax for your If and SUM??

Please show the SQL for your query.

You said initially you were trying to convert Local Currency into USD. Is that still the cae?

[That_Guy]

Records pulled from WO Extensions Table. In this table I need to use the following fields: Currency, Total Cost (Local Currency) and Total Cost (USD).

Total COST (USD) field needs to be updated based on the Currency Field and the Total Cost (Local Currency) Fields.

Sample Data of what would be included in Currency Field is ARS.
ARS is representative of the local currency, in this case it would be the Argentine Peso which has a conversion rate of 0.212268

Sample Data included in Total Cost (Local Currency) field would be 50,000

Total Cost (USD) needs to be calculated using Total Cost (Local Currency)*0.212268
This would equal $10,613.40

There will be various currencies, about 12, each which needs to be converted into US Dollars. Obviously exchange rates change daily, but I can update the rate manually weekly to simplify implementing this.

The Syntax I am using for IF and SUM is what I built in SharePoint, which does not seem to translate into Access.

Hope this makes sense. I have extreme novice Access experience and about none in VBA coding or creating modules. Therefore I was hoping I could have Access do the calculation and make an Access Table which I can then pull into SharePoint.

I need Cost USD to be calculated based on

[orange]

A couple of points.

You don't store Totals or calculated fields in tables. Tables contain atomic/individual data. Your create the total by running a query against the table and summing the individual fields.

I have acc2003 and you have 2010 - so there are some differences. Will your IT group allow you/give you permissions to get data from the internet. If you have access to the Timegenie.com/forex.xml site (or another site with the exchange rate data), you can automate the process to do currency conversions.

What is WO Extensions table?

Can you post a jpg of your relationships window?

Please describe your situation more. What are all of your tables?
What exactly are you trying to do in plain English? Can you give an example?

[rpeare]

The issue with sharepoint is that you can't use VB code. You have to run everything with macros/queries but you can still perform the calculations we're talking about without using VB (query level) if you change the formula in the END AMOUNT field of my example to:

=IIf((DLookUp("[Exchange_Rate]","ExchangeRates","[ID] = " & Forms!frm_test!Fld_ExchTo))=1,[fld_ConvAmt]*(DLookUp("[Exchange_Rate]","ExchangeRates","[ID] = " & Forms!frm_test!Fld_ExchFrom)),IIf((DLookUp("[Exchange_Rate]","ExchangeRates","[ID] = " & Forms!frm_test!Fld_ExchFrom))=1,[fld_ConvAmt]/(DLookUp("[Exchange_Rate]","ExchangeRates","[ID] = " & Forms!frm_test!Fld_ExchTo)),([fld_ConvAmt]*(DLookUp("[Exchange_Rate]","ExchangeRates","[ID] = " & Forms!frm_test!Fld_ExchFrom)))/(DLookUp("[Exchange_Rate]","ExchangeRates","[ID] = " & Forms!frm_test!Fld_ExchTo))))

You can perform the calculation without any VB code at all, it's all SQL statements or formulas, Though I would likely perform this a different way if I were designing it for me you get the idea. Automating your conversion rates is really the way to go even if you have a slight markup (for services rendered) you can program that into your database as well. All that aside you can see that it's possible to arrive at what you want with a formula. I'm also including a couple of queries to show you how you can do it that way as well, it's very basic (probably not as elegant as it could be, but it doesn't use the dlookup function which is a memory/resource hog). The first is a cartesian query that basically builds a matrix of every possible conversion from and conversion to country then the second is performing the actual conversion based on the two countries you choose on the main form.

Qry_ExchangeRate_Cartesian is the matrix
Qry_ConversionFinal is the query that will show your end payout in the currency of the convert TO field.

Conversion.zip