How to extract data from Trading View into Google Sheets

Here’s how to export data from Trading View and convert into useful statistics using Google Sheets

Statistical Edge

Let me show you how simple it is to gather some meaningful and useful stats on the market you trade.

I cover this in more depth in module 10 of my course.

But let me give you the basics now so even if you choose not to buy the course you can still get rolling.

  
Ok, so why would a discretionary trader need to get any statistics on the market?

Well, if you know the historical tendencies of a market you can potentially adjust your trade entry, stops, or target accordingly.

What do I mean?

Let’s say the market has dipped through prior lows, you grabbed a nice long and are working out a good trade target.

How about prior day highs? Seems like a good target.

Maybe – but if you knew that the market only hit the prior day’s high AND low just 8% of the time maybe you’re asking a bit much?

Context matters of course, but knowing the historical odds can help you gauge if you’re asking too much from this trade.

Another angle:

You’re long on a 5-minute opening range breakout.

Where’s your target?

Well, you might be tempted to snatch that quick 30 ticks on the DOW.

But if you knew the typical 1st 15-minute range on the DOW was 75 ticks and the 1st 15-minute candle still had 5 minutes to go, you would probably feel more comfortable holding the trade.

No need to snatch at 30 ticks, if the momentum is on your side and typically the market does a 75-tick range in the 1st 15 minutes you’d be tempted to let it run right?

You see what I’m getting at. (I made those numbers BTW.)

There are loads of stats you can grab to help your trading.

  • Range
  • Prior swing touches
  • Overnight levels
  • Volume
  • Time

The list is endless.

Anyway, let me give you a quick guide on how to gather simple but useful stats on the market for yourself right now. (I’ll use Trading View.)

Let’s imagine we trade the S&P 500 and want to see some data around price testing prior day highs and lows.

The percentage time that price hit the prior high, low, both, and neither should do.

We’ll use SPY as a good data source because we don’t want the overnight season for this sample.

S&P 500 is basically the SPY.

If the SPY hits the prior day high then the S&P 500 will have. (And it strips out overnight highs and lows.)

Firstly, select your market and download the data

Load up a daily chart of SPY and zoom out so you have a year or two on display.

Now click the little arrow next to your layout name. (No laughing at my naming convention at the back, it’s a white chart, simple…)

Then select export chart data.

I like to select ISO time stamp, then hit export.

You get something like this:

Now let’s create columns in F, G and H and title them:

Prior Low, Prior High, Both

Assuming your data layout is the same as mine add these formulas

In F3 add:

=IF((D3<=D2), “Hit Prior Low”, “Did Not Hit”)

In G3 add:

=IF((C3>=C2), “Hit Prior High”, “Did Not Hit”)

In H3 add:

=IF(AND(F3=”Hit Prior Low”, G3=”Hit Prior High”), “Hit Both”, IF(OR(F3=”Hit Prior Low”, G3=”Hit Prior High”), “Hit One”, “Hit Neither”))

Fill those formulas down and you’ll have three new columns that look like this:

Now we need to add up all the occurrences and check against the total number of days we downloaded to get some stats.

Ok so in my example I have 454 days of data for SPY.

I use this formula to count up the number of days we hit the prior day’s low:
=COUNTIF(F:F, “Hit Prior Low”)

This one for the high
=COUNTIF(G:G, “Hit Prior High”)

This one for both
=COUNTIF(H:H, “Hit Both”)

And this for neither
=COUNTIF(H:H, “Hit Neither”)

You can add these to any cell you want.

Those formulas will count all the occurrences for the correct column.

Now simply divide the formula result by the total number of days (for me it was 454.)

Hit the % button to convert to a %

Et Voila

You now have some simple stats on your market.

  • 50% of the time SPY hit or exceeded the prior low and not the high
  • 52% the prior high and not the low
  • 10% both high and low (outside day)
  • 8% neither (inside day)
  • 82% we hit one or the other.

Interesting huh?

You can do this for any market, and go as complex or as simple as you like.

Anyway, something a bit different and hopefully useful to your trading.

Remember to double-check my formulas and any data you gather 🙂 I’m not a quant…