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

Synthetic currency pair

December 13, 2011 by Shaun Overton Leave a Comment

A synthetic currency pair allows you to reduce the spread costs of trading. If your broker charges an arm and a leg on on the cross currencies and you trade more than a few micro lots, you can potentially save a few pips by creating the cross currency on your own rather than paying for exorbitant spreads. This is especially true if your forex broker falsely classifies itself as an MT4 forex ECN. Many of these types offer very low spreads on the majors, but retain the high mark ups commonly associated with cross currencies.

To determine if this makes sense for your situation, you need to calculate the pip values of the spread of the offered cross currency and the spread cost of your synthetic currency pair.

Say, for example, that you trade GBP/JPY. Your broker charges a hefty, though common, spread of 7 pips. Trading one standard lot presently works out to:
$100,000 = JPY 7,500,000
A 1 pip change is worth JPY 1,000, which is worth $13.33 per pip. The 7 pip spread costs $13.33 * 7 = $93 per trade. Ouch.

synthetic

How to make a synthetic currency pair

Now, we take the more cost effective approach. You want to choose two currency pairs that cancel each other out, yet offer the lowest trading cost possible. The obvious candidates are USD/JPY and GPB/USD for the synthetic cross.

Cancelling out the pairs is pretty straight forward. The goal consists of multiplying something expensive with something cheaper to save on costs.

Expensive cross currencyReplacement currency
GBPUSD
/*/
JPYUSD

Swap the terms on the bottom. Doing so changes nothing mathematically, but does everything to clarify the siutation to any forex trader.

GBPUSD
/*/
JPYUSD

We must evaluate the cost of the synthetic candidates now that we know which pairs to consider trading. If the GBPUSD costs 2.5 pips to trade and the USDJPY costs 2 pips, the total cost is

$25 in spread on 1 standard lot of GBPUSD
$26.66 in spread on 1 standard lot of USDJPY. We must increase the lot size to 1.55 lots, however, to ensure that we sell as much yen as we bought in pounds. The final cost for the USJPY equals $41.32.

Trading a synthetic pair only costs $41.32 + 25 = $66.32. Trading directly from the broker costs $93. Although it takes a lot of extra effort, I highly recommend this approach if you’re not trying to scalp or quickly enter positions.

Overpaying for a trade by nearly 30% is wholly unjustified. You put so much effort into developing a trading strategy. Earning profits in forex is hard enough as it is. Stop overpaying for your trades; the broker is taking advantage of either your laziness or ignorance when you do.

Filed Under: How does the forex market work?, Uncategorized Tagged With: currency, pair, spread, synthetic

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.