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



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


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)

 
Search this Thread
 

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

(login for full post details)
  #1 (permalink)
Strat Enthusiast
Chiang Mai Thailand
 
Experience: Beginner
Platform: Seirra Chart
Broker: Optimus + Rithmic
Trading: MNQ
 
ninjus's Avatar
 
Posts: 711 since Jun 2017
Thanks: 1,130 given, 2,262 received

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"

Follow me on Twitter Visit my futures io Trade Journal Reply With Quote

Can you help answer these questions
from other members on futures io?
Range bar differences in Tradestation vs. TOS
TradeStation
Do You Have A PlayBooks/What Form Does It Take?
Traders Hideout
Macros for flatten and reverse
TradeStation
FX Futures have been nagging me for a while
Currencies
QDEL
Stocks and ETFs
 
 
(login for full post details)
  #2 (permalink)
Strat Enthusiast
Chiang Mai Thailand
 
Experience: Beginner
Platform: Seirra Chart
Broker: Optimus + Rithmic
Trading: MNQ
 
ninjus's Avatar
 
Posts: 711 since Jun 2017
Thanks: 1,130 given, 2,262 received

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"

Follow me on Twitter Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #3 (permalink)
Chicago IL
 
 
Posts: 73 since Apr 2016
Thanks: 183 given, 48 received



ninjus View Post
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.

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...

Reply With Quote
The following 2 users say Thank You to userque for this post:
 
(login for full post details)
  #4 (permalink)
Mumbai, India
 
Experience: Beginner
Platform: Own Customized
Trading: Crude, NIFTY, BANKNIFTY
 
LastDino's Avatar
 
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.

Reply With Quote
The following user says Thank You to LastDino for this post:
 
(login for full post details)
  #5 (permalink)
Site Moderator
Sarasota FL
 
Experience: Advanced
Platform: Sierra Chart
Trading: ES, YM
 
bobwest's Avatar
 
Posts: 5,966 since Jan 2013
Thanks: 45,965 given, 19,999 received


LastDino View Post
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
Visit my futures io Trade Journal Reply With Quote
The following user says Thank You to bobwest for this post:
 
(login for full post details)
  #6 (permalink)
Mumbai, India
 
Experience: Beginner
Platform: Own Customized
Trading: Crude, NIFTY, BANKNIFTY
 
LastDino's Avatar
 
Posts: 448 since Jan 2019
Thanks: 1,101 given, 1,118 received


bobwest View Post
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.

Reply With Quote


futures io Trading Community Platforms and Indicators > [Other]       Help converting a SC spreadsheet formula into Tradingview's pine script


September 11, 2020


Upcoming Webinars and Events
     



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