The most popular exchange rates in Our Country are exchange rates.
True, they teach little, but they punish a lot.
If you often need to find out the dollar exchange rate for a certain given date in the past (an order or delivery date, for example), then this macro will save you a lot of time. Instead of going to the archives of the Central Bank, it will be enough to launch it.
Open the Visual Basic Editor by clicking OTHER+F11 or by selecting on the tab developer Command Visual Basic editor (Developer — Visual Basic), insert a new module (menu Insert – Module) and copy the text of this macro there:
Sub GetDollar() Dim sURI As String, oHttp As Object, htmlcode, outstr As String, inpdate As Date 'displays a date dialog inpdate = CDate(InputBox("Enter date in DD.MM.YYYY format", "Course dollars", Date)) 'we form a string for the web request sURI = "https://cbr.ru/currency_base/daily/?UniDbQuery.Posted=True&UniDbQuery.To=" & Format(inpdate, "DD.MM.YYYY" ) 'make a request On Error Resume Next Set oHttp = CreateObject("MSXML2.XMLHTTP") If Err.Number <> 0 Then Set oHttp = CreateObject("MSXML.XMLHTTPRequest") End If On Error GoTo 0 If oHttp Is Nothing Then Exit Sub End If oHttp.Open "GET", sURI, False oHttp.Send 'get the HTML page with rates and extract the dollar rate from it htmlcode = oHttp.responseText outstr = Mid(htmlcode, InStr(InStr(1, htmlcode, "USD" ), htmlcode, "") - 22, 7) Set oHttp = Nothing 'replace the dot with a comma and display it in the active cell outstr = Replace(outstr, ",", ".") ActiveCell.Value = outstr End Sub Now, if you close the Visual Basic editor and return to Excel, then through the menu Service – Macro – Macros, or by pressing ALT+F8 we can run our macro GetDollar, enter the date in the window that appears and get the dollar rate for the given date in the current cell. For ease of launch, you can make a macro button on the toolbar or on a sheet, or assign a keyboard shortcut to the macro.
In fact, the macro loads data from the archive of the website of the Central Bank of the Federation, therefore, to insert the rate into the current cell, you must have Internet access (at the moment). The data is inserted as constants (no link or update).
To get the exchange rate of the euro, yen, pound and other currencies, you only need to change the currency code “USD” in the code to any one you need, for example, “EUR”, “KZT”, etc.
- Import exchange rates from the Internet with automatic updating
- What are macros. How and where to insert macro text in Visual Basic.
- Insert any currency rate on any date using the PLEX add-on
- CBR function from PLEX add-on