Help converting a SC spreadsheet formula into Tradingview's pine script - futures io

# Discussion in Platforms and Indicators

Updated by LastDino
Top Posters
1. looks_one LastDino with 2 posts (1 thanks)
2. looks_two ninjus with 2 posts (0 thanks)
3. looks_3 userque with 1 posts (2 thanks)
4. looks_4 bobwest with 1 posts (1 thanks)
1. trending_up 1,425 views
2. thumb_up 4 thanks given
3. group 3 followers
1. forum 5 replies
2. attach_file 0 attachments

 Welcome to futures io: the largest futures trading community on the planet, with well over 100,000 members
 Genuine reviews from real traders, not fake reviews from stealth vendors Quality education from leading professional traders We are a friendly, helpful, and positive community We do not tolerate rude behavior, trolling, or vendors advertising in posts We are here to help, just let us know what you need You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple. -- Big Mike, Site Administrator (If you already have an account, login at the top of the page)

Strat Enthusiast
Chiang Mai Thailand

Experience: Beginner
Platform: Seirra Chart
Broker: Optimus + Rithmic

Posts: 711 since Jun 2017

My meager coding skills stretch as far as putting together basic excel type formulas into SC spreadsheet studies.

I have one formula here that outputs either a 1 or -1.

If we output a 1 background color is green. -1 and it is red.

In SC cell K I have this:

=IF(AND(C4>C5,D4<D5,C3>C4,D3>=D4),1,
IF(AND(C4>C5,D4<D5,C3<=C4,D3<D4),-1,
IF(AND(C4>C5,D4>=D5,C3>C4,D3>=D4),1,
IF(AND(C4<=C5,D4<D5,C3<=C4,D3<D4),-1,
IF(E3>C4,1,
IF(E3<D4,-1,
K4))))))

Which is in effect this:

IF(AND(high[-1]>high[-2],low[-1]>=low[-2],high[0]>high[-1],low[0]>=low[-1]),1,
IF(AND(high[-1]<=high[-2],low[-1]<low[-2],high[0]<=high[-1],low[0]<low[-1]),-1,
IF(close[0]>high[-1],1,
IF(close[0]<low[-1],-1,
previous result))))))

Which gives me this type of background coloring:

I've tried messing around with pinescript and else if statements but I'm not getting anywhere and don't want to keep wasting hours on something that might take someone a little smarter than me minutes.

Was looking at something along the lines of this but like I say its not happening.

trend = if high[1] > high[2] and low[1] < low[2] and high > high[1] and low >= low[1]
1
else if high[1] > high[2] and low[1] < low[2] and high <= high[1] and low < low[1]
-1

......
else
trend[1]

//Background
col = trend == 1 ? lime : red
bgcolor(col, transp = 80)

If someone can help that you be great.

Cheers

Marcus Aurelius
 "Not to assume it's impossible because you find it hard. But to recognize that if it's humanly possible, you can do it too"

Can you help answer these questions
from other members on futures io?
 Range bar differences in Tradestation vs. TOSTradeStation Do You Have A PlayBooks/What Form Does It Take?Traders Hideout Macros for flatten and reverseTradeStation FX Futures have been nagging me for a whileCurrencies QDELStocks and ETFs

in the last 7 days on futures io
 Finally Turning the Corner, tha "its 80% Psychology" thing...35 thanks Lost & losing hope26 thanks Voter Sentiment in upcoming US election and effect on market after election results22 thanks Emini 4 Tick Scalp still valid?16 thanks Coronavirus COVID-1912 thanks

Strat Enthusiast
Chiang Mai Thailand

Experience: Beginner
Platform: Seirra Chart
Broker: Optimus + Rithmic

Posts: 711 since Jun 2017

It's a work in progress but have this so far which is getting close.

trend = high[1] > high[2] and low[1] < low[2] and high > high[1] and low >= low[1] ? color.lime :
high[1] > high[2] and low[1] < low[2] and high <= high[1] and low < low[1] ? color.red :
high[1]>high[2] and low[1]>=low[2] and high>high[1] and low>=low[1] ? color.lime :
high[1]<=high[2] and low[1]<low[2] and high<=high[1] and low<low[1] ? color.red :
high[1]>high[2] and low[1]>low[2] and high<=high[1] and low>=low[1] ? color.lime :
high[1]>high[2] and low[1]<low[2] and high<=high[1] and low>=low[1] and close[1]>open[1] ? color.lime :
high[1]<high[2] and low[1]<low[2] and high<=high[1] and low>=low[1] ? color.red :
high[1]>high[2] and low[1]<low[2] and high<=high[1] and low>=low[1] and close[1]<open[1] ? color.red :
close[0]>high[1] ? color.lime :
close[0]<high[1] ? color.red : color.lime
bgcolor(trend)

I don't know how to have the trend variable reference itself and it's prior result if no conditions are matched so I'm going to have to create a condition for all scenarios but should get the end result I need once I get time to do that properly.

Sure there is a better way but I'm not smart enough to figure that out.

Marcus Aurelius
 "Not to assume it's impossible because you find it hard. But to recognize that if it's humanly possible, you can do it too"

Chicago IL

Posts: 73 since Apr 2016

ninjus
 It's a work in progress but have this so far which is getting close. trend = high[1] > high[2] and low[1] < low[2] and high > high[1] and low >= low[1] ? color.lime : high[1] > high[2] and low[1] < low[2] and high <= high[1] and low < low[1] ? color.red : high[1]>high[2] and low[1]>=low[2] and high>high[1] and low>=low[1] ? color.lime : high[1]<=high[2] and low[1]high[2] and low[1]>low[2] and high<=high[1] and low>=low[1] ? color.lime : high[1]>high[2] and low[1]=low[1] and close[1]>open[1] ? color.lime : high[1]=low[1] ? color.red : high[1]>high[2] and low[1]=low[1] and close[1]high[1] ? color.lime : close[0]

Consider using

Code
 `code tags.`

Consider posting the whole code between the code tags, if it's not confidential.

When you get errors, consider posting screenshots of any errors that are displayed.

Great job with your progress so far!

If I understand you correctly, you may be able to use another variable to hold the old trend. Not positive how it should be done in pine script, but if you are able to post the whole code, between code tags, I may look into it this weekend.

But for now, here's the general idea: Store the old value in a different variable, before the new value is calculated. This way, you'll have access to the old and the new values.

Code
 ```oldTrend=trend trend = high[1] > high[2] and low[1] < low[2] and high > high[1] and low >= low[1] ? color.lime : ...etc...```

 The following 2 users say Thank You to userque for this post:

 (login for full post details) #4 (permalink) Elite Member Mumbai, India   Experience: Beginner Platform: Own Customized Trading: Crude, NIFTY, BANKNIFTY     Posts: 448 since Jan 2019 Thanks: 1,101 given, 1,118 received We have coders exclusive group of "pinescripters" on telegram, if and when you run into such an issue where you could use a little suggestion from others, do consider joining there. I won't provide links to avoid breaking any rules, if any, just letting you know since it's rare to see people here be interested in TV coding. You can ask for group link directly in TV help chat window too.
 The following user says Thank You to LastDino for this post:

Site Moderator
Sarasota FL

Platform: Sierra Chart

Posts: 5,966 since Jan 2013

LastDino
 We have coders exclusive group of "pinescripters" on telegram, if and when you run into such an issue where you could use a little suggestion from others, do consider joining there. I won't provide links to avoid breaking any rules, if any, just letting you know since it's rare to see people here be interested in TV coding. You can ask for group link directly in TV help chat window too.

Your instinct was correct. We do not permit external discussion group links on FIO. The groups may be completely legitimate, and I'm sure you wouldn't have mentioned one that isn't, but our issue here is that it opens an opportunity for unscrupulous vendors to have a shot at future.io members for advertising and promotional purposes, far away from our ability to moderate their content.

I understand your intent was to be helpful. But we feel we need to handle things this way.

Bob.

 When one door closes, another opens. -- Cervantes, Don Quixote
 The following user says Thank You to bobwest for this post:

Mumbai, India

Experience: Beginner
Platform: Own Customized

Posts: 448 since Jan 2019

bobwest
 Your instinct was correct. We do not permit external discussion group links on FIO. The groups may be completely legitimate, and I'm sure you wouldn't have mentioned one that isn't, but our issue here is that it opens an opportunity for unscrupulous vendors to have a shot at future.io members for advertising and promotional purposes, far away from our ability to moderate their content. I understand your intent was to be helpful. But we feel we need to handle things this way. Bob.

Yup, that's always an issue. And it is completely understandable, if I was running the forum I would do the same. For exactly same reasons we also don't call that group "official" help forum, because its a free social media grp outside of TV itself and is free for all and we can't do anything if someone scams someone else.

Its an unfortunate aspect of social media.

 futures io > [Other]       Help converting a SC spreadsheet formula into Tradingview's pine script

September 11, 2020

 Upcoming Webinars and Events Become an Elite Member

 Copyright © 2020 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, info@futures.io All information is for educational use only and is not investment advice.There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts