This Excel spreadsheet downloads historical Forex data from the Internet, specifically the daily bid rates.
You simply enter two three-letter currency symbols, two dates, and click a button.
The spreadsheet will then connect to a remote website, and download daily bid rates (using some clever VBA). The Forex data is imported into a sheet called “Data”.
Now you can analyze and plot the forex data using all of Excel’s functionality. As an example, the spreadsheet plots the exchange rate data. If you wanted, you could easily add Bollinger Bands to the plot.
I’ve password-protected the VBA module in the spreadsheet (I’m heavily modifying it for a future post), but if you want the password then let me know.
This is free Forex data that we’re downloading, and in no way shape or form compares with paid services. Using the VBA programming principles used in the spreadsheet, you can easily use other tools, like Access databases, instead of Excel.
You may also be interested in this spreadsheet which downloads historical stock prices from Yahoo straight into Excel.
The spreadsheet is in Excel 97-2003 format, although I’ve only tested it in Excel 2010.
Update 29th July 2011: I’ve removed the password. Enjoy, and please come back and let me know what cool Forex applications you’ve made with it.
Download Excel Spreadsheet for automatically importing forex data from the Internet
I am interested in working this code into an existing workbook I have made. This looks like an awesome tool and will do most of what I need it to do. How can I go about getting that password for the VBA code though so I can validate a few things?
Thanks
Hi, I am really interested in the code,
im having a few problems getting my workbook to do the same thing but with stock data.
@Brent and @kristers: I've removed the password from the spreadsheet (just download it again). Let me know what you super cool applications you come up with!
Hi
It looks like a terrific tool to use with excell. However, I opened the link to the spread sheet and try just to chage dates but excell won’t update because “the macro is either not available in the work book or may be disabled”
Any suggestion?
thx
A
I’ve just tried the spreadsheet in Excel 2010 and it works perfectly for me.
When you loaded the spreadsheet in Excel, did you give the macro permission to run?
How do you modify the code so that you can query more than one “To Currency” at a time? So, suppose you want to get the FX rates for the conversion from GBP to up to 10 different currencies at the same time rather than changing the “To Currency and re-running over and over?
I tried the worksheet. I get an error
“Compile Error: Variable not defined” on this line
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
This item is highlited: xlSortOnValues
I’ve just tested the worksheet on 64 bit Excel 2010 / Win, and it works as expected. What version of Excel/windows are you using?
I tried to run this on my WinXP Excel 2003
and i had same error as Jim got,
“Compile Error: Variable not defined”
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Hi,
I found your code very useful and have embedded it into one of my sheets. I would suggest updating the connection on each refresh rather than creating a new one as all of the connections will be refreshed on open unless this property is turned off on creation. The following code will achieve this:
Dim FX_table As QueryTable
Set FX_table = Sheets(strShtName).QueryTables(1)
FX_table.Connection = “URL;” & str
FX_table.Refresh
Another approach would be to delete all existing connections before adding a new one:
Public Sub RemoveQueries(shtName As String)
Dim ws As Worksheet
Dim qt As QueryTable
Set ws = Sheets(shtName)
For Each qt In ws.QueryTables
qt.Delete
Next qt
End Sub
It is possible to modify to show the open, high, low, and close?
Thanks (in advance),
Lance
Hi, works fantastic for me. I only had a problem using it on a german Excel version as the decimal separator was not correctly recognized. This could be overcome letting Excel know which character to recognize as the decimal separator. Maybe you could include it in your version to help other users who maybe jump into the same problem:
Sheets(“Data”).Range(“a5″).CurrentRegion.TextToColumns Destination:=Sheets(“Data”).Range(“a5″), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, other:=True, OtherChar _
:=”,”, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1)), DecimalSeparator:=”.”, ThousandsSeparator:=”‘”, _
TrailingMinusNumbers:=True
Thomas, your solution for “non-US” Windows works great.
Thanks a lot, you saved me plenty of time.
Thanks a lot for this very useful tool!
Just a remark on my side: you first set all calculations to manual and I personally prefer having them automatic, so I just added the following at the end of the GetData() macro:
Application.Calculation = xlCalculationAutomatic
Thanks again!