title: Get exchange rates from a certain date with Excel VBA
body: <p>I would like to create an Excel function that can get the exchange rate between two given currencies at a given date. Something like: =GetFX(&quot;USD&quot;,&quot;GBP&quot;,&quot;31/12/2020&quot;)</p>
  <p>I found the below code on a <a href="https://www.reddit.com/r/excel/comments/4onnkq/exchange_rates_from_a_certain_date_automatically/" rel="nofollow noreferrer">Reddit thread</a> which is exactly doing that, except that it is quite outdated and no longer work with Oanda. I don't know how to use Excel VBA with external data and could not figure out what is wrong. I would very much appreciate if someone knows how to fix it.</p>
  <p>Thanks!</p>
  <pre><code>Function GetOandaFX(fromCurr As String, toCurr As String, AsofDate As Date) As String
  
  Dim oHttp As Object
  Dim sURL As String
  Dim HTMLdoc As Object
  Dim TDelements As Object
  Dim TDelement As Object
  
  ' Create an XMLHTTP object
  Set oHttp = CreateObject(&quot;MSXML2.XMLHTTP&quot;)
           
  ' get the URL to open
   sURL = &quot;https://www.oanda.com/fx-for-business/historical-rates?&quot; _
      &amp; &quot;date=&quot; &amp; AsofDate _
      &amp; &quot;date_fmt=us&quot; _
      &amp; &quot;&amp;exch=&quot; &amp; fromCurr _
      &amp; &quot;&amp;sel_list=&quot; &amp; toCurr _
      &amp; &quot;&amp;value=1&amp;format=HTML&amp;redirected=1&quot;
    
  ' open connection and get website html
  oHttp.Open &quot;GET&quot;, sURL, False
  oHttp.send
   
  Set HTMLdoc = CreateObject(&quot;htmlfile&quot;)
  
  With HTMLdoc
      If oHttp.readyState = 4 And oHttp.Status = 200 Then 'readystate checks loading, status checks the validity of URL
  
  '    ' assign the returned text to a HTML document
      .body.innerHTML = oHttp.responseText
        
      Set TDelements = .getElementsByTagName(&quot;TD&quot;)
      'Loop within Table elements
      For Each TDelement In TDelements
          If RateFound = True Then
              GetOandaFX = TDelement.innerText
              Exit For
          End If
          If TDelement.innerText = toCurr Then RateFound = True
      Next
      End If
  End With
  
  Set oHttp = Nothing
  
  End Function
  </code></pre>