Microsoft Excel Programming

[Techfan1128]

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?

 

[halcyonguy]

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.

 

[Techfan1128]

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"?

 

[halcyonguy]

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

 

[halcyonguy]

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.

# 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);
#

 

[Techfan1128]

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.

# 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);
#

Thanks. Ill check it out tonight!

 

[Techfan1128]

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.

# 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);
#

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

 

[halcyonguy]

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

 

[Techfan1128]

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