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=
Database of Purchasing Power Parity
The file that you can download below extracts data from FRED for different currencies and inflation rates. From a selected starting point (by you), you can evaluate how an exchange rate would evolve compared to actual exchange rates. This is a big deal in project finance because the indexing of purchase power contracts and availability contracts to a currency like USD or Euro, works effectively if the currency change is driven by the difference in inflation rates. The PPP formula is Base x (1+local Inflation)/(1+USD Inflation). You can download the database by clicking on the button below.
After downloading the file, the first page should look something like the picture below. You can update the database by first clearing the sheets and then pressing the read all button. Once you have updated the data (if you want to), you can go to the page with the two graphs that demonstrates the PPP analysis and allows you to choose different currencies.
The euro and USD illustrates the analysis below. The date I selected is when the euro data was reported. This shows that there is less appreciation in the euro tan would be explained by inflation.
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 .