Microsoft Excel Programming

Techfan1128

New member
I have a spreadsheet set up to have TOS feed into it live. Every 15 minutes I have to go in manually and see what the numbers are and chart it manually. What I need is someone who can program the TOS Data coming into the spreadsheet to chart Every X minutes (Default will be 5 minutes). So what I have feeding into the spread sheet is a stock scanner with Certain stocks that I want in it. I am measuring how many of them are positive or negative just to get a general market sentiment in the moment.

So the goal would be for the spreadsheet to chart automatically every 5 minutes and if 66% of the stocks are positive the Line is Green, If 34-65% are positive the line is Orange and if 33% or less are positive the line is red. This isnt really designed for trade entry, just market sentiment broken down by individual stocks in a particular basket of stocks (I have several lists that I have looked at and watch and have created).

Is there anyone out there who can help with this?

g9ZzKzU.png
 
Last edited:
i think this could be done in TOS , if you don't have too many symbols in a list. show a % number , in a colored label, on a chart.

by default , excel pulls data from tos and overwrites the previous data that was saved in excel. you need a vba script to automatically copy the data to another location, to a new row. to save all the data. then you can compare current data to previous data.
i worked on this a long time ago. i can look for my files and see if i got it to work.
 
i think this could be done in TOS , if you don't have too many symbols in a list. show a % number , in a colored label, on a chart.

by default , excel pulls data from tos and overwrites the previous data that was saved in excel. you need a vba script to automatically copy the data to another location, to a new row. to save all the data. then you can compare current data to previous data.
i worked on this a long time ago. i can look for my files and see if i got it to work.
Yes, I would be curious to know what you find in your old Files. Also, what do you mean by the first Line "I think this could be done in TOS, If you dont have too many symbols in a list. Show a % number, in a colored label, on a chart"?
 
Yes, I would be curious to know what you find in your old Files. Also, what do you mean by the first Line "I think this could be done in TOS, If you dont have too many symbols in a list. Show a % number, in a colored label, on a chart"?
it seems that you want to collect a bunch of data, then generate 1 number and 1 color. a tos study , with a label,, could do that, assuming you don't need to look at that excel graph line and that there isn't a list of 30 stocks. your example has 12 stocks, so all 12 of them would be entered into 1 study. maybe 3 labels would tell you what you want. green with the green % , orange with the orange % , red with the red %.
 
Yes, I would be curious to know what you find in your old Files. Also, what do you mean by the first Line "I think this could be done in TOS, If you dont have too many symbols in a list. Show a % number, in a colored label, on a chart"?
here is a tos study that looks at the 12 stocks in your example.
it compares the current bar price, to the previous bar close, to determine a price change.
can choose to display 12 labels, of the stocks and their change.
the 3 labels on the right, show a % of the total for gainers, losers, and those staying the same.

it is doable to do in TOS, but as the quantity of symbols gets larger, there will be more , longer, formulas.

Python:
# diffmanysymbols_01

# ---------------
# 21-05-30
# halcyonguy
# --------------
# a study to look at 12 stocks and determine if they are up or down, from previous bar
#  use short variable names, to reduce formula lengths

def qty = 12;

# stock symbols
input s1 = "MCD";
input s2 = "MMM";
input s3 = "MRK";
input s4 = "MSFT";
input s5 = "NKE";
input s6 = "PG";
input s7 = "TRV";
input s8 = "UNH";
input s9 = "V";
input s10 = "VZ";
input s11 = "WBA";
input s12 = "WMT";

#  prices for symbols
def p1 = close(s1);
def p2 = close(s2);
def p3 = close(s3);
def p4 = close(s4);
def p5 = close(s5);
def p6 = close(s6);
def p7 = close(s7);
def p8 = close(s8);
def p9 = close(s9);
def p10 = close(s10);
def p11 = close(s11);
def p12 = close(s12);


# add in a $$ tolerance for having no change.   if within 0.02 then considered same.
input price_tolerance = 0.02;
def tol = price_tolerance;

def d1 = if (p1 - p1[1] > tol ) then 1 else if ( p1[1] - p1 > tol ) then -1 else 0;
def d2 = if (p2 - p2[1] > tol ) then 1 else if ( p2[1] - p2 > tol ) then -1 else 0;
def d3 = if (p3 - p3[1] > tol ) then 1 else if ( p3[1] - p3 > tol ) then -1 else 0;
def d4 = if (p4 - p4[1] > tol ) then 1 else if ( p4[1] - p4 > tol ) then -1 else 0;
def d5 = if (p5 - p5[1] > tol ) then 1 else if ( p5[1] - p5 > tol ) then -1 else 0;
def d6 = if (p6 - p6[1] > tol ) then 1 else if ( p6[1] - p6 > tol ) then -1 else 0;
def d7 = if (p7 - p7[1] > tol ) then 1 else if ( p7[1] - p7 > tol ) then -1 else 0;
def d8 = if (p8 - p8[1] > tol ) then 1 else if ( p8[1] - p8 > tol ) then -1 else 0;
def d9 = if (p9 - p9[1] > tol ) then 1 else if ( p9[1] - p9 > tol ) then -1 else 0;
def d10 = if (p10 - p10[1] > tol ) then 1 else if ( p10[1] - p10 > tol ) then -1 else 0;
def d11 = if (p11 - p11[1] > tol ) then 1 else if ( p11[1] - p11 > tol ) then -1 else 0;
def d12 = if (p12 - p12[1] > tol ) then 1 else if ( p12[1] - p12 > tol ) then -1 else 0;


input show_stocks_diff = no;

addlabel(show_stocks_diff , ">>", color.gray);
addlabel(show_stocks_diff, s1 + " " + (p1 - p1[1]), ( if d1 > 0 then color.green else if d1 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s2 + " " + (p2 - p2[1]), ( if d2 > 0 then color.green else if d2 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s3 + " " + (p3 - p3[1]), ( if d3 > 0 then color.green else if d3 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s4 + " " + (p4 - p4[1]), ( if d4 > 0 then color.green else if d4 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s5 + " " + (p5 - p5[1]), ( if d5 > 0 then color.green else if d5 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s6 + " " + (p6 - p6[1]), ( if d6 > 0 then color.green else if d6 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s7 + " " + (p7 - p7[1]), ( if d7 > 0 then color.green else if d7 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s8 + " " + (p8 - p8[1]), ( if d8 > 0 then color.green else if d8 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s9 + " " + (p9 - p9[1]), ( if d9 > 0 then color.green else if d9 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s10 + " " + (p10 - p10[1]), ( if d10 > 0 then color.green else if d10 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s11 + " " + (p11 - p11[1]), ( if d11 > 0 then color.green else if d11 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s12 + " " + (p12 - p12[1]), ( if d12 > 0 then color.green else if d12 < 0 then color.red else color.orange));


# add up similar quantities
def up = (d1 == 1)+(d2 == 1)+(d3 == 1) + (d4 == 1)+(d5 == 1)+(d6 == 1) + (d7 == 1)+(d8 == 1)+(d9 == 1) + (d10 == 1)+(d11 == 1)+(d12 == 1);

def dwn = (d1 == -1)+(d2 == -1)+(d3 == -1) + (d4 == -1)+(d5 == -1)+(d6 == -1) + (d7 == -1)+(d8 == -1)+(d9 == -1) + (d10 == -1)+(d11 == -1)+(d12 == -1);

def same = (d1 == 0)+(d2 == 0)+(d3 == 0) + (d4 == 0)+(d5 == 0)+(d6 == 0) + (d7 == 0)+(d8 == 0)+(d9 == 0) + (d10 == 0)+(d11 == 0)+(d12 == 0);

# calc each total as a % of the quantity of 12
def up_per = round((up/qty)*100,1);
def dwn_per = round((dwn/qty)*100,1);
def same_per = round((same/qty)*100,1);


addlabel(1, "<<", color.gray);
addlabel(1, up_per + "%", color.green);
addlabel(1, same_per + "%", color.orange);
addlabel(1, dwn_per + "%", color.red);
addlabel(1, ">>", color.gray);
#

veSRUQ4.jpg
 
here is a tos study that looks at the 12 stocks in your example.
it compares the current bar price, to the previous bar close, to determine a price change.
can choose to display 12 labels, of the stocks and their change.
the 3 labels on the right, show a % of the total for gainers, losers, and those staying the same.

it is doable to do in TOS, but as the quantity of symbols gets larger, there will be more , longer, formulas.

Python:
# diffmanysymbols_01

# ---------------
# 21-05-30
# halcyonguy
# --------------
# a study to look at 12 stocks and determine if they are up or down, from previous bar
#  use short variable names, to reduce formula lengths

def qty = 12;

# stock symbols
input s1 = "MCD";
input s2 = "MMM";
input s3 = "MRK";
input s4 = "MSFT";
input s5 = "NKE";
input s6 = "PG";
input s7 = "TRV";
input s8 = "UNH";
input s9 = "V";
input s10 = "VZ";
input s11 = "WBA";
input s12 = "WMT";

#  prices for symbols
def p1 = close(s1);
def p2 = close(s2);
def p3 = close(s3);
def p4 = close(s4);
def p5 = close(s5);
def p6 = close(s6);
def p7 = close(s7);
def p8 = close(s8);
def p9 = close(s9);
def p10 = close(s10);
def p11 = close(s11);
def p12 = close(s12);


# add in a $$ tolerance for having no change.   if within 0.02 then considered same.
input price_tolerance = 0.02;
def tol = price_tolerance;

def d1 = if (p1 - p1[1] > tol ) then 1 else if ( p1[1] - p1 > tol ) then -1 else 0;
def d2 = if (p2 - p2[1] > tol ) then 1 else if ( p2[1] - p2 > tol ) then -1 else 0;
def d3 = if (p3 - p3[1] > tol ) then 1 else if ( p3[1] - p3 > tol ) then -1 else 0;
def d4 = if (p4 - p4[1] > tol ) then 1 else if ( p4[1] - p4 > tol ) then -1 else 0;
def d5 = if (p5 - p5[1] > tol ) then 1 else if ( p5[1] - p5 > tol ) then -1 else 0;
def d6 = if (p6 - p6[1] > tol ) then 1 else if ( p6[1] - p6 > tol ) then -1 else 0;
def d7 = if (p7 - p7[1] > tol ) then 1 else if ( p7[1] - p7 > tol ) then -1 else 0;
def d8 = if (p8 - p8[1] > tol ) then 1 else if ( p8[1] - p8 > tol ) then -1 else 0;
def d9 = if (p9 - p9[1] > tol ) then 1 else if ( p9[1] - p9 > tol ) then -1 else 0;
def d10 = if (p10 - p10[1] > tol ) then 1 else if ( p10[1] - p10 > tol ) then -1 else 0;
def d11 = if (p11 - p11[1] > tol ) then 1 else if ( p11[1] - p11 > tol ) then -1 else 0;
def d12 = if (p12 - p12[1] > tol ) then 1 else if ( p12[1] - p12 > tol ) then -1 else 0;


input show_stocks_diff = no;

addlabel(show_stocks_diff , ">>", color.gray);
addlabel(show_stocks_diff, s1 + " " + (p1 - p1[1]), ( if d1 > 0 then color.green else if d1 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s2 + " " + (p2 - p2[1]), ( if d2 > 0 then color.green else if d2 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s3 + " " + (p3 - p3[1]), ( if d3 > 0 then color.green else if d3 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s4 + " " + (p4 - p4[1]), ( if d4 > 0 then color.green else if d4 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s5 + " " + (p5 - p5[1]), ( if d5 > 0 then color.green else if d5 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s6 + " " + (p6 - p6[1]), ( if d6 > 0 then color.green else if d6 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s7 + " " + (p7 - p7[1]), ( if d7 > 0 then color.green else if d7 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s8 + " " + (p8 - p8[1]), ( if d8 > 0 then color.green else if d8 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s9 + " " + (p9 - p9[1]), ( if d9 > 0 then color.green else if d9 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s10 + " " + (p10 - p10[1]), ( if d10 > 0 then color.green else if d10 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s11 + " " + (p11 - p11[1]), ( if d11 > 0 then color.green else if d11 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s12 + " " + (p12 - p12[1]), ( if d12 > 0 then color.green else if d12 < 0 then color.red else color.orange));


# add up similar quantities
def up = (d1 == 1)+(d2 == 1)+(d3 == 1) + (d4 == 1)+(d5 == 1)+(d6 == 1) + (d7 == 1)+(d8 == 1)+(d9 == 1) + (d10 == 1)+(d11 == 1)+(d12 == 1);

def dwn = (d1 == -1)+(d2 == -1)+(d3 == -1) + (d4 == -1)+(d5 == -1)+(d6 == -1) + (d7 == -1)+(d8 == -1)+(d9 == -1) + (d10 == -1)+(d11 == -1)+(d12 == -1);

def same = (d1 == 0)+(d2 == 0)+(d3 == 0) + (d4 == 0)+(d5 == 0)+(d6 == 0) + (d7 == 0)+(d8 == 0)+(d9 == 0) + (d10 == 0)+(d11 == 0)+(d12 == 0);

# calc each total as a % of the quantity of 12
def up_per = round((up/qty)*100,1);
def dwn_per = round((dwn/qty)*100,1);
def same_per = round((same/qty)*100,1);


addlabel(1, "<<", color.gray);
addlabel(1, up_per + "%", color.green);
addlabel(1, same_per + "%", color.orange);
addlabel(1, dwn_per + "%", color.red);
addlabel(1, ">>", color.gray);
#

veSRUQ4.jpg
Thanks. Ill check it out tonight!
 
here is a tos study that looks at the 12 stocks in your example.
it compares the current bar price, to the previous bar close, to determine a price change.
can choose to display 12 labels, of the stocks and their change.
the 3 labels on the right, show a % of the total for gainers, losers, and those staying the same.

it is doable to do in TOS, but as the quantity of symbols gets larger, there will be more , longer, formulas.

Python:
# diffmanysymbols_01

# ---------------
# 21-05-30
# halcyonguy
# --------------
# a study to look at 12 stocks and determine if they are up or down, from previous bar
#  use short variable names, to reduce formula lengths

def qty = 12;

# stock symbols
input s1 = "MCD";
input s2 = "MMM";
input s3 = "MRK";
input s4 = "MSFT";
input s5 = "NKE";
input s6 = "PG";
input s7 = "TRV";
input s8 = "UNH";
input s9 = "V";
input s10 = "VZ";
input s11 = "WBA";
input s12 = "WMT";

#  prices for symbols
def p1 = close(s1);
def p2 = close(s2);
def p3 = close(s3);
def p4 = close(s4);
def p5 = close(s5);
def p6 = close(s6);
def p7 = close(s7);
def p8 = close(s8);
def p9 = close(s9);
def p10 = close(s10);
def p11 = close(s11);
def p12 = close(s12);


# add in a $$ tolerance for having no change.   if within 0.02 then considered same.
input price_tolerance = 0.02;
def tol = price_tolerance;

def d1 = if (p1 - p1[1] > tol ) then 1 else if ( p1[1] - p1 > tol ) then -1 else 0;
def d2 = if (p2 - p2[1] > tol ) then 1 else if ( p2[1] - p2 > tol ) then -1 else 0;
def d3 = if (p3 - p3[1] > tol ) then 1 else if ( p3[1] - p3 > tol ) then -1 else 0;
def d4 = if (p4 - p4[1] > tol ) then 1 else if ( p4[1] - p4 > tol ) then -1 else 0;
def d5 = if (p5 - p5[1] > tol ) then 1 else if ( p5[1] - p5 > tol ) then -1 else 0;
def d6 = if (p6 - p6[1] > tol ) then 1 else if ( p6[1] - p6 > tol ) then -1 else 0;
def d7 = if (p7 - p7[1] > tol ) then 1 else if ( p7[1] - p7 > tol ) then -1 else 0;
def d8 = if (p8 - p8[1] > tol ) then 1 else if ( p8[1] - p8 > tol ) then -1 else 0;
def d9 = if (p9 - p9[1] > tol ) then 1 else if ( p9[1] - p9 > tol ) then -1 else 0;
def d10 = if (p10 - p10[1] > tol ) then 1 else if ( p10[1] - p10 > tol ) then -1 else 0;
def d11 = if (p11 - p11[1] > tol ) then 1 else if ( p11[1] - p11 > tol ) then -1 else 0;
def d12 = if (p12 - p12[1] > tol ) then 1 else if ( p12[1] - p12 > tol ) then -1 else 0;


input show_stocks_diff = no;

addlabel(show_stocks_diff , ">>", color.gray);
addlabel(show_stocks_diff, s1 + " " + (p1 - p1[1]), ( if d1 > 0 then color.green else if d1 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s2 + " " + (p2 - p2[1]), ( if d2 > 0 then color.green else if d2 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s3 + " " + (p3 - p3[1]), ( if d3 > 0 then color.green else if d3 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s4 + " " + (p4 - p4[1]), ( if d4 > 0 then color.green else if d4 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s5 + " " + (p5 - p5[1]), ( if d5 > 0 then color.green else if d5 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s6 + " " + (p6 - p6[1]), ( if d6 > 0 then color.green else if d6 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s7 + " " + (p7 - p7[1]), ( if d7 > 0 then color.green else if d7 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s8 + " " + (p8 - p8[1]), ( if d8 > 0 then color.green else if d8 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s9 + " " + (p9 - p9[1]), ( if d9 > 0 then color.green else if d9 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s10 + " " + (p10 - p10[1]), ( if d10 > 0 then color.green else if d10 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s11 + " " + (p11 - p11[1]), ( if d11 > 0 then color.green else if d11 < 0 then color.red else color.orange));
addlabel(show_stocks_diff, s12 + " " + (p12 - p12[1]), ( if d12 > 0 then color.green else if d12 < 0 then color.red else color.orange));


# add up similar quantities
def up = (d1 == 1)+(d2 == 1)+(d3 == 1) + (d4 == 1)+(d5 == 1)+(d6 == 1) + (d7 == 1)+(d8 == 1)+(d9 == 1) + (d10 == 1)+(d11 == 1)+(d12 == 1);

def dwn = (d1 == -1)+(d2 == -1)+(d3 == -1) + (d4 == -1)+(d5 == -1)+(d6 == -1) + (d7 == -1)+(d8 == -1)+(d9 == -1) + (d10 == -1)+(d11 == -1)+(d12 == -1);

def same = (d1 == 0)+(d2 == 0)+(d3 == 0) + (d4 == 0)+(d5 == 0)+(d6 == 0) + (d7 == 0)+(d8 == 0)+(d9 == 0) + (d10 == 0)+(d11 == 0)+(d12 == 0);

# calc each total as a % of the quantity of 12
def up_per = round((up/qty)*100,1);
def dwn_per = round((dwn/qty)*100,1);
def same_per = round((same/qty)*100,1);


addlabel(1, "<<", color.gray);
addlabel(1, up_per + "%", color.green);
addlabel(1, same_per + "%", color.orange);
addlabel(1, dwn_per + "%", color.red);
addlabel(1, ">>", color.gray);
#

veSRUQ4.jpg
I dont know if i'll have to wait until US market is open for it to work, but so far its not printing anything. Ill see what it does in the morning
 
I dont know if i'll have to wait until US market is open for it to work, but so far its not printing anything. Ill see what it does in the morning
it shows labels at the top of a chart. those should show up.
the default is to just show 5 labels, 2 gray, and in between them, a green, orange, and red.
if you go to study settings and change this input to yes, then 12 more labels will show up
show_stocks_diff = no
 
it shows labels at the top of a chart. those should show up.
the default is to just show 5 labels, 2 gray, and in between them, a green, orange, and red.
if you go to study settings and change this input to yes, then 12 more labels will show up
show_stocks_diff = no
thanks for that update. i watched it today and wondered what was wrong with it

looking forward to watching it again tomorrow
 

Join useThinkScript to post your question to a community of 21,000+ developers and traders.

Similar threads

Not the exact question you're looking for?

Start a new thread and receive assistance from our community.

87k+ Posts
196 Online
Create Post

Similar threads

Similar threads

The Market Trading Game Changer

Join 2,500+ subscribers inside the useThinkScript VIP Membership Club
  • Exclusive indicators
  • Proven strategies & setups
  • Private Discord community
  • ‘Buy The Dip’ signal alerts
  • Exclusive members-only content
  • Add-ons and resources
  • 1 full year of unlimited support

Frequently Asked Questions

What is useThinkScript?

useThinkScript is the #1 community of stock market investors using indicators and other tools to power their trading strategies. Traders of all skill levels use our forums to learn about scripting and indicators, help each other, and discover new ways to gain an edge in the markets.

How do I get started?

We get it. Our forum can be intimidating, if not overwhelming. With thousands of topics, tens of thousands of posts, our community has created an incredibly deep knowledge base for stock traders. No one can ever exhaust every resource provided on our site.

If you are new, or just looking for guidance, here are some helpful links to get you started.

What are the benefits of VIP Membership?
VIP members get exclusive access to these proven and tested premium indicators: Buy the Dip, Advanced Market Moves 2.0, Take Profit, and Volatility Trading Range. In addition, VIP members get access to over 50 VIP-only custom indicators, add-ons, and strategies, private VIP-only forums, private Discord channel to discuss trades and strategies in real-time, customer support, trade alerts, and much more. Learn all about VIP membership here.
How can I access the premium indicators?
To access the premium indicators, which are plug and play ready, sign up for VIP membership here.
Back
Top