Of course, I am open to suggestions. The goal is to complete a spreadsheet of 5 pages of 50 columns and 50 rows. The first column is for the date followed by 7 groups of seven columns. 2 pages are dedicated to index or ETD and have 18 groups of three columns (information for these two pages is taken from Barchart Snapshot Opinion site (see below). The frame exists. The routine has
1) to delete row number 2, all other rows going up one rank making row num 50 free to be completed with numbers and some uppercase letters with a sign,
2) put today date in cell column A, row 50 in four digits (American style = 0330 = March 30th),
3) to open the site of IBD Stock Chechup for a particular stock (of course the name of the stock is put on the top of the first column of each group of seven columns and will change depending of the choice of the stock analysed. Maybe this could be done manually! for example [login to view URL]
4) the information I would like to have from IBD Stock Checkup are the:
1) Composite Rating
2) Acc/Dist. Rating
3) Relative Strength Rating
4) Industry Group Rank with the indication of the rank of the stock - more precisely: if the stock is on the top five or not. The name of the group leaders is given just next to the Performance table. AAPL for example will rank this way as of 033112: 38/1 (Industry Group Rank 38 and number one on the "Composite - Group Leaders)
4a) and put all these informations in
1) column 1 of the group (column 1 has the name of the stock) for the Composite Rating
2) column Acc/Dis for the Acc/Dist. Rating
3) colum RS for the Relative Strength Rating
5) to open the site of Barchart Snapshot Opinion ([login to view URL]
6) the information I want like to have from Barchart Snapshot Opinion are the percentage, (given by a number, so I just need the number) of:
1) Today's Opinion
2) Current Ratings: Overall Strength and Direction: Strength
3) Current Ratings: Overall Strength and Direction: Direction
6a) and put all these informations in
1) column BART of the group for the Today's Opinion
2) column STR for the Overall Strength and Direction: Strength
3) colum DIR for the Overall Strength and Direction: Direction
7) I want to give a color to the cell containing the information in columns 1 (name of the stock), Acc/Dis, BART and IBDG;
8) Color column Name of the stock:
1) from 1 to 69, cell color red
2) from 70 t0 79 color camel
3) from 80 to 89 color green light
4) from 90 to 99 color green dark
9) Color column Acc/Dis:
1) E, cell color red,
2) D, cell color red,
3) C, cell color red,
4) B- cell color red light
5) B, cell color blue light
6) B+, cell color blue royal,
7) A-, cell color green light,
8) A, cell color green dark
9) A+, cell color green dark.
10) When the rate of Acc/Dis is going up, a sign / is also put in the cell,
11) When the rate of Acc/Dis is going down, a sign \ is also put in the cell.
12) Color column RS (same as for Name)
1) from 1 to 69, cell color red
2) from 70 t0 79 color camel
3) from 80 to 89 color green light
4) from 90 to 99 color green dark
13) Color for column IBDG:
a) when number are going up, anyway, color red light
b) when number are going down, anyway, color green light
14) Color column BAR (letter in blue royal dark)
a) -100 to -24 red
b) -16, -8, 0, red light,
c) 8, 16, 24, 32,40, 48, blue light
d) 56, blue royal,
e) 64, 72, 80, green light,
f) 88, 96, 100, green dark
15) Color coloumn STR, none unless a 100 appears, then color green dark
16) Color column DIR, none unless a 100 appears, then color green dark.
17) For the two pages with 18 groups of three columns, colors are the same as for
colum BAR, STR and DIR.
18) Budget: USD 200 - 300