Algorithmic and Mechanical Forex Strategies | OneStepRemoved

  • Articles
  • Sophisticated Web Sites
  • Automated Trading
  • Testimonials
  • Contact

Using Excel to Receive Real Time Forex Data from Yahoo! Finance

March 4, 2014 by Kalen Smith 9 Comments

“Procrastination has been called a thief,–the thief of time. I wish it were no worse than a thief. It is a murderer.”

Every Forex trader should live by this quote from William Nevins. Your opportunities deteriorate with every second that you delay making a decision. Traders that have access to real-time data have a huge advantage over the rest of the market.

There are some premium tools on the market, but you don’t need to invest in them. You can export real time data from Yahoo! Finance for free. I found a great VBA script that can do this for any currency pairs that you are trying to track.

Using Yahoo! Finance to Get Real-Time Forex Data

Many Forex traders use Yahoo! Finance to monitor currency prices. Unfortunately, the site isn’t perfect. The biggest limitation of Yahoo Finance! is that prices aren’t listed in real time, but Joshua Radcliffe has created a VBA script that gets around that. Here are some steps to use it to get real-time prices on currency pairs.

  • Open up Microsoft Excel
  • Click on the Macros tab and select the View Macros option
  • Create a name for your Macro in the box
  • Click Create
  • Add the code listed at the bottom of this section into the code editor
  • Change the values in currency 1 and currency 2 to the currency pairs that you would like to monitor. For example, you could set currency1 = “EUR” and currency2 = “USD” if you want to see the price between the dollar the euro. You can also keep the code as is and reference the values for the currencies in the cells shown in the code. However, my solution is easier if you are following a specific currency pair.
  • Click View Macros again to select the Run option
  • The real-time data will be shown in cell C9

This script will give you all the real-time data that you need including the market price, the ask price, the bid price, the 1-year target estimate and the beta-coefficient. You can run the program as many times as you would like.

Forexmacroresults

Here is the code that you will need to add:

Sub Macro1()

‘

‘ Macro1 Macro

‘ Provided by Joshua Radcliffe

‘ www.JoshuaRadcliffe.com

 

Dim currency1 As String

Dim currency2 As String

 

currency1 = Cells(4, 3).Value

currency2 = Cells(5, 3).Value

 

Range(“B9:C12”).Select

Selection.ClearContents

 

With ActiveSheet.QueryTables.Add(Connection:= _

“URL;http://finance.yahoo.com/q?s=” & currency1 & currency2 & “=X”, Destination:=Range(“$B$9”))

.Name = “q?s=” & currency1 & currency2 & “=X_1”

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.WebSelectionType = xlSpecifiedTables

.WebFormatting = xlWebFormattingNone

.WebTables = “””table1″””

.WebPreFormattedTextToColumns = True

.WebConsecutiveDelimitersAsOne = True

.WebSingleBlockTextImport = False

.WebDisableDateRecognition = False

.WebDisableRedirections = False

.Refresh BackgroundQuery:=False

End With

 

End Sub

I have tested Radcliffe’s code for the CHF/JPY currency pair. The prices are slightly different than those listed on Yahoo! Finance. This shows that Daniel’s script works as claimed.

Applications of this Data

There are a couple of reasons that this data can be useful. First of all, you can use this script to get real time pricing data on your currency pairs. This gives you a significant advantage over traders that are relying on Yahoo! Finance’s charts, because they have a 15-minute lag before prices are updated.

You can also record prices throughout the day and use a variety of Excel tools to observe pricing trends. Run the macro and record the price data in a different cell each time. You can select all of the prices and use them to create a two-dimensional line graph in Excel. If you haven’t used Excel before, simply follow these steps:

  • Select the prices in the cell (they should all be organized in a column)
  • Click on the Insert tab
  • Click on 2D line to create a graph with the data that you selected
  • You can copy and paste the various charts that you create into a separate document where you can see them later

You can also run a regression analysis. You will need to go to the Excel options tab and click Analysis Tool Pack. You will then need to select Tools and then click Add –Ins. After you have followed these steps, you can click Regression Analysis from the Data Analysis tab.

You may want to monitor pricing trends near popular trading hours. I would recommend monitoring pricing trends between 8 GMT (3 AM EST) and 9 GMT (4 AM EST), because that is one of the most popular trading times. It will take some discipline to wake up that time every day if you are living in the East Coast on the United States, but every bit of knowledge is worth the sacrifice. You can gather approximately 20 data points during that timeframe to draw a trend line. This will give you a better understanding of the trading behavior of the rest of the community.

If you want to get particularly detailed then you can create separate line graphs for different days of the week. You may need a couple months to collect this data, but it will give you a significant advantage over other traders.

Are There Other Options to Get Real Time Data?

There are other tools available to get real-time price data on currency pairs. However, there are a couple of reasons that I would recommend this VBA program instead.

First of all, you don’t have to pay to use this script. This is a great advantage for beginning Forex traders that don’t want to invest a lot of money.

The script also makes it much easier to observe trends. Most other tools that offer real-time Forex data are streaming tools. They may help you make decisions based on the current trading price, but it can be difficult to copy the data and use it to create line charts.

Overall, I would recommend this script over any of the other real-time Forex tools on the market.

Filed Under: How does the forex market work? Tagged With: currency, excel, macro, Yahoo Finance

Finding Free Stock Data

September 24, 2013 by Timothy Lewkow 2 Comments

I spent the past two years writing an algorithmic trading system that ran in my bedroom while I left the house, worked my day job, and lived my life. The idea took several months to derive, implement, and perfect while overcoming more obstacles that I could have imagined. By far, the largest problem I have encountered is finding good, free stock data.

Stock Tick Data

The smallest increment of free data I could find is on a Norwegian website that can be read in English by searching “NetFonds” in Google and selecting “Translate this page.” Along with currency and commodity data, NetFonds also has tick data for all NASDAQ, NYSE, and AMEX stocks. Getting the data for free is easy, but takes some effort to understand.

free stock data

Getting free stock data is as refreshing as a day at the beach.

To start, enter the following URL into your browser

http://hopey.netfonds.no/tradedump.php?date=[date]&paper=[stock]&csv_format=txt

Here, there are two parameters that you need to alter.

[Date] – Should be replace with a date in the form YYYYMMDD, so for example 20130919 would be the data obtained from Thursday, September 19, 2013. In my experience, data goes back around 15 days, but I can’t guarantee this for every stock. Generally, I take and store yesterday’s data today.

[Stock] – This is where you replace the name of the ticker to collect. The catch is that you must know the exchange code.

NYSE code is ‘N’ — for example, to collect Macy’s, [stock] = M.N
NASDAQ code is ‘O’ — for example, to collect Google, [stock] = GOOG.O
AMEX code is ‘A’ — for example, you get the picture

The data displayed has a time, price, and quantity in .txt format. Everything looks self explanatory, expect for the time column. I elaborate by example of the first entry I see.

time = 20130919T153000

Translated as 2013, 09 (sept), 19 (day), Time, 15:30:00

which seems weird, but remember, you are collecting data from a Norwegian website and Oslo is six hours ahead of New York City time. Considering military time format, 15:30:00 is really 3:30 in Norway, which is 9:30 in EST and the market open. Notice that under this logic, the last data point during open market hours is represented by the string

time = 20130919T220000

By far, the largest problem I have encountered is finding good free data

You will also notice that some rows have identical time stamps. This should be interpreted chronologically with the logic that the price is changing several times per second. Recall how price changes.

Finally, I want to note that all times outside 15:30 and 22:00 are after hours transactions. You can always see after market activity on Google finance. Try searching for Apple, and check “Extended Hours” under the settings link under the given chart. Grey prices are transactions that occurred after hours.

Order Book Tick Data

The best free tick order book data I could find displays only the best bid and ask quotes for a given time. Nevertheless, there are endless ways this information could be used to improve a system.

Again on NetFonds, try pasting the following URL into your browser:

http://hopey.netfonds.no/posdump.php?date=[date]&paper=[stock]&csv_format=txt

with the same date and stock convention used above. Notice you have a few extra columns corresponding to volume and best bid/ask in the market.

For this data set, you will see that extended hours quotes extend far more than in tick data, though the spread widens considerably. Extended hours trading is considered risky due to this lack of liquidity, but this is a topic of it’s own.

Google Finance Data

Data can be found here, and follows very similar conventions to NetFonds though data comes in every minute. The URL is

http://www.google.com/finance/getprices?i=[PERIOD]&p=[DAYS]d&f=d,o,h,l,c,v&df=cpct&q=[TICKER]

[PERIOD] – Time interval in seconds
[DAYS] – Historical data period. For example [DAYS] = 10d asks for the last ten days
[TICKER] – The stock symbol. No codes necessary, so AAPL works just fine

Yahoo Finance Data

Similar to Google Finance and Yahoo, the general URL is given by

http://chartapi.finance.yahoo.com/instrument/1.0/[TICKER]/chartdata;type=quote;range=1d/csv

Frequency is seconds, and historical range available is 5 days.

Obtaining the Data

Programming languages have an age old trade off. If you want a fast language, you have to sacrifice in learning non-trivial languages and concepts. If you want a code that downloads the above data sets, and you want it to work tomorrow, you have to sacrifice in using a slower language.

For me, Mathematica and Python were extremely intuitive to use on day one, and both have built in functions to browse and download data. I also learned to use Apple Script on my mac with very little effort. This was nice because I could program my computer to wake up in the morning, go to a website, and download the latest data.

The speed trade off from not using a language like C++ was assumed away for me. Unless you pay top dollar, you have to assume that the data you are downloading is somewhat perturbed and there is nothing you can do about it.

Filed Under: Test your concepts historically Tagged With: data, equities, Google Finance, stocks, tick data, Yahoo Finance

FREE trading strategies by email

Trending

Sorry. No data so far.

Archives

  • Dominari
  • How does the forex market work?
  • Indicators
  • MetaTrader Tips
  • MQL (for nerds)
  • NinjaTrader Tips
  • Pilum
  • QB Pro
  • Stop losing money
  • Test your concepts historically
  • Trading strategy ideas
  • Uncategorized
  • What's happening in the current markets?

Translation


Free Trading Strategies

Privacy PolicyRisk Disclosure

Copyright © 2023 OneStepRemoved.com, Inc. All Rights Reserved.