Exchange rate data is important for many reasons. For example, in acquiring stock price data, the data is generally expressed in terms of local prices; you probably want a common currency to compare the stock indicies. Finding historic exchange rates for countries such as the Philippines can be tricky and are not reported by the St. Louis Fed. To get the prices there are some alternatives one of which is http://www.x-rates.com/historical/?from=USD&amount=1&date=
Current Exchange Rate Database and Internet Read
The read current currency file is used as an introduction to use of a macro to read data from the internet using the WORKBOOKS.OPEN statement so that you can record information from a website into an excel file. The example below and the video read data from a commonly used currency website that contains current exchange rates http://www.xe.com/currencyconverter/convert/?From=USD&To=CHF (I think when you go to google and ask for the Euro to Swiss exchange rate it takes you here to this website). I the completed file below, you can select different exchange rates and press a button (Read Currencies in List) to get current updates of the exchange rate. I originally used the list box technique that is further explained on the flexible graph page is used so you can change the number of currencies that you may want in a report. But now in excel 2016, list boxes are dangerous so I have changed the technique to use check boxes with TRUE and FALSE and Match. I use this macro to find current exchange rates to develop invoices or other analyses that require current data on exchange rates.
If you download the file below, all you have to do is click on the currencies you want with the check boxes. Then click the macro box and all of your exchange rates are ready to be copied into any other file that you want.
The video below shows you how to create a macro that finds the correct place in the file that is downloaded from the internet with the workbooks.open statement and how to set-up the file to download multiple exchange rates.
Historic Database with More Currencies than Included in FRED Website
Technical VBA Details of the Files that Automatically Read Currencies
Sub read_all() Dim start_number, end_number current_status = Application.Calculation Application.Calculation = xlCalculationManual start_number = InputBox(" Start Number") end_number = InputBox(" End Number") For i = start_number To end_number Range("code").Calculate Range("code") = i history_exchange Next i Application.Calculation = current_status End Sub
The detailed reading of a single currency is shown below. This uses a basic copy and paste.
. Sub history_exchange() Application.DisplayAlerts = False base_book = ActiveWorkbook.Name base_sheet = ActiveSheet.Name num_sheets = Sheets.Count Range("url").Calculate Workbooks.Open (Range("url")) temp_name = ActiveWorkbook.Name Cells.Select Selection.Copy Windows(base_book).Activate Sheets.Add After:=Sheets(num_sheets) Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False On Error Resume Next Range("sheet_name").Calculate ActiveSheet.Name = Range("sheet_name") Workbooks(temp_name).Close End Sub .