Skip to main content

Знание срещу незнание

Кратък експеримент с помощта на Google 🙂

„не знам“ – около 2 370 000 резултата
„знам“ – около 4 820 000 резултата

Тъй като първите резултати са подмножество на вторите, вадим 2 370 000 от 4 820 000 и откриваме истинското съотношение:

„знам“ – 2 450 000 (50.83%)
„не знам“ – 2 370 000 (49.17%)

Хората, които си мислят, че знаят, са малко повече от хората, които си мислят, че не знаят.

Currency conversion in Excel

I have an Excel workbook which calculates money amounts in multiple currencies, and I thought that it would be very nice to make it automatically get the newest exchange rates over the Internet.

I have always been using Google as my favorite currency conversion tool. You can try that yourself – for example, if you insert „100 GBP in USD“, it will show you the value of $100 in pounds sterling.

And so I was happy to learn that the XMLHTTP ActiveX control, which is very popular among web developers, can be accessed from Excel’s VBA macros too. That control is basically used to retrieve data from a HTTP server.

So it actually proved very simple to code an Excel macro that would connect to Google, get currency exchange rate data, parse it and put it in a cell.

Here’s the function that does the actual job:

Function GetCurrencyRate(Currency_1 As String, Currency_2 As String)
    Dim XMLhttp: Set XMLhttp = CreateObject("microsoft.xmlhttp")
    url$ = "" + Currency_1 + "+in+" + Currency_2

    XMLhttp.Open "GET", url$, False
    XMLhttp.send ""

    Result$ = XMLhttp.responsetext

    CBegin% = InStr(Result$, "<font size=+1><b>") + 17
    Result$ = Mid$(Result$, CBegin%)

    CBegin% = InStr(Result$, " = ") + 3
    Result$ = Mid$(Result$, CBegin%)

    GetCurrencyRate = Val(Result$)
End Function

As long as Google keeps the same page design, this function will work perfectly. Now any macro can call the currency exchange rate retrieval function and use the results. Here is the solution I chose for my case – an Auto_Open macro, which would automatically fire up when the workbook is opened, get the US Dollar / Euro exchange rate and put it in the F9 cell on Sheet 1:

Sub Auto_Open()
    R = GetCurrencyRate("USD", "EUR")
    Sheets("Sheet1").Range("F9").Formula = Str$(R)
End Sub