Oldtimer trying to learn

[MarcoSicily]

Hello Everyone,
I'm a rather new user to Excel but I have been learning alot through self study. I found this forum today an I am hoping someone can explain how I would develop a formula to help with analyzing stock dividend information.
I have copied and pasted stock data into the excel workbook. What I am trying to develop is a formula that will tell me how many days it will take for the daily HIGH to be equal or above the price I am buying it at. To be awarded a Dividend you must buy the stock no later than the CLOSE on the day before the Ex-Dividend day.
On the attached example The Ex-Dividend date is on Aug. 15th so we would buy the CLOSING price the day before - August 14 - which would be at a price of $15.07. As you can see the answer to my question of when we could first sell at a price of $15.07 or higher is August 30th. which equals 15 days from the buy date.
What I ultimately want to figure out is how many months can I buy the night before the Dividend and sell it at a profit or breakeven within X amount of days. Also I want to average how many days , on average , it takes to buy and sell successfully.
I would appreciate any comments that might help me accomplish this analysis.
Thank you,
Marco

Date	Open	High	Low	Close
15-Sep-17	15.09	15.12	15.09	15.12
14-Sep-17	15.1	15.12	15.09	15.12
13-Sep-17	15.11	15.11	15.09	15.1
12-Sep-17	15.09	15.11	15.08	15.11
11-Sep-17	15.05	15.09	15.05	15.08
8-Sep-17	15.07	15.08	15.01	15.02
7-Sep-17	15.1	15.11	15.06	15.09
6-Sep-17	15.09	15.1	15.08	15.1
5-Sep-17	15.09	15.11	15.06	15.08
1-Sep-17	15.09	15.11	15.08	15.1
31-Aug-17	15.06	15.09	15.05	15.08
30-Aug-17	15.03	15.07	15	15.04
29-Aug-17	15	15.05	14.99	15.01
28-Aug-17	15.03	15.03	15	15.01
25-Aug-17	15.01	15.03	15.01	15.02
24-Aug-17	15.02	15.03	15	15.01
23-Aug-17	15	15.02	14.99	15
22-Aug-17	15.02	15.04	15	15
21-Aug-17	15.04	15.04	15	15.01
18-Aug-17	14.98	15.03	14.97	15.03
17-Aug-17	15.03	15.03	14.98	14.98
16-Aug-17	15.04	15.04	15.01	15.02
15-Aug-17	15.02	15.02	15	15.03
15-Aug-17	0.07 Dividend
14-Aug-17	15.04	15.09	15.02	15.07
11-Aug-17	15.01	15.02	14.96	15
10-Aug-17	15.11	15.12	14.98	15
9-Aug-17	15.15	15.15	15.11	15.12
8-Aug-17	15.17	15.17	15.14	15.15
7-Aug-17	15.18	15.2	15.15	15.16
4-Aug-17	15.18	15.18	15.15	15.15
3-Aug-17	15.17	15.18	15.15	15.17
2-Aug-17	15.17	15.18	15.14	15.16
1-Aug-17	15.14	15.17	15.13	15.17
31-Jul-17	15.14	15.15	15.12	15.14
28-Jul-17	15.11	15.13	15.1	15.12
27-Jul-17	15.13	15.13	15.09	15.09
26-Jul-17	15.08	15.12	15.08	15.12
25-Jul-17	15.15	15.16	15.09	15.09
24-Jul-17	15.14	15.15	15.14	15.15
21-Jul-17	15.13	15.14	15.1	15.13
20-Jul-17	15.11	15.12	15.08	15.12
19-Jul-17	15.09	15.13	15.08	15.09
18-Jul-17	15.07	15.09	15.05	15.09
17-Jul-17	15.08	15.09	15.05	15.06
14-Jul-17	15.08	15.09	15.07	15.09
14-Jul-17	0.071 Dividend
13-Jul-17	15.17	15.18	15.13	15.13
12-Jul-17	15.15	15.17	15.11	15.16
11-Jul-17	15.15	15.16	15.12	15.13
10-Jul-17	15.15	15.17	15.14	15.14
7-Jul-17	15.15	15.18	15.13	15.16
6-Jul-17	15.14	15.17	15.12	15.14
5-Jul-17	15.13	15.16	15.11	15.15
3-Jul-17	15.13	15.16	15.1	15.15
30-Jun-17	15.1	15.12	15.09	15.11
29-Jun-17	15.12	15.13	15.06	15.09
28-Jun-17	15.11	15.13	15.1	15.13
27-Jun-17	15.13	15.13	15.1	15.11
26-Jun-17	15.11	15.14	15.1	15.12
23-Jun-17	15.09	15.1	15.07	15.1
22-Jun-17	15.06	15.1	15.06	15.08
21-Jun-17	15.05	15.08	15.05	15.06
20-Jun-17	15.05	15.07	15.03	15.07
19-Jun-17	15.04	15.06	15.03	15.05
16-Jun-17	15.05	15.05	15.02	15.02
15-Jun-17	15.04	15.05	15.03	15.04
15-Jun-17	0.071 Dividend
14-Jun-17	15.09	15.12	15.08	15.11
13-Jun-17	15.08	15.08	15.05	15.07
12-Jun-17	15.07	15.09	15.05	15.07
9-Jun-17	15.08	15.1	15.05	15.06
8-Jun-17	15.07	15.08	15.05	15.06
7-Jun-17	15.08	15.09	15.06	15.07
6-Jun-17	15.08	15.11	15.06	15.07
5-Jun-17	15.09	15.1	15.07	15.08
2-Jun-17	15.08	15.1	15.07	15.09
1-Jun-17	15.04	15.08	15.03	15.08
31-May-17	15.06	15.06	15.03	15.05
30-May-17	15.06	15.06	15.03	15.06
26-May-17	15.02	15.06	15.01	15.06
25-May-17	15.03	15.03	14.99	15.02
24-May-17	15.02	15.03	14.98	15.02
23-May-17	14.99	15.02	14.98	15.02
22-May-17	14.97	14.99	14.96	14.98
19-May-17	14.97	14.99	14.94	14.96
18-May-17	14.93	14.97	14.92	14.96
17-May-17	14.98	14.99	14.95	14.95
16-May-17	14.99	15.01	14.97	15.01
15-May-17	14.99	15	14.98	14.99
15-May-17	0.071 Dividend
12-May-17	15.04	15.06	15.02	15.06
11-May-17	15.04	15.04	15.02	15.03
10-May-17	15.01	15.05	15.01	15.04
9-May-17	15.04	15.04	15.01	15.04
8-May-17	15	15.03	14.99	15.03
5-May-17	14.99	15.01	14.96	14.99
4-May-17	15.01	15.01	14.97	14.99
3-May-17	14.99	15.01	14.97	15.01
2-May-17	14.97	15	14.95	14.98
1-May-17	14.97	14.98	14.94	14.97
28-Apr-17	14.97	14.98	14.95	14.95
27-Apr-17	14.96	14.97	14.94	14.96
26-Apr-17	14.96	14.96	14.94	14.95
25-Apr-17	14.95	14.98	14.95	14.96
24-Apr-17	14.97	14.97	14.94	14.95
21-Apr-17	14.91	14.94	14.9	14.94
20-Apr-17	14.92	14.92	14.89	14.91
19-Apr-17	14.92	14.93	14.89	14.91
18-Apr-17	14.93	14.93	14.89	14.93
17-Apr-17	14.9	14.94	14.9	14.94
13-Apr-17	14.9	14.92	14.87	14.88
13-Apr-17	0.07 Dividend
12-Apr-17	14.95	14.97	14.93	14.97
11-Apr-17	15	15	14.92	14.94
10-Apr-17	14.95	15	14.95	14.99
7-Apr-17	14.99	14.99	14.95	14.97
6-Apr-17	14.9	14.97	14.89	14.97
5-Apr-17	14.96	14.98	14.9	14.91
4-Apr-17	14.93	14.97	14.92	14.95
3-Apr-17	14.86	14.98	14.83	14.95
31-Mar-17	14.8	14.83	14.78	14.82
30-Mar-17	14.78	14.81	14.76	14.79
29-Mar-17	14.77	14.81	14.76	14.78
28-Mar-17	14.77	14.78	14.75	14.77
27-Mar-17	14.73	14.76	14.72	14.76
24-Mar-17	14.76	14.77	14.73	14.74
23-Mar-17	14.71	14.76	14.69	14.75
22-Mar-17	14.66	14.72	14.65	14.72
21-Mar-17	14.7	14.7	14.64	14.67
20-Mar-17	14.68	14.7	14.67	14.69
17-Mar-17	14.69	14.71	14.67	14.69
16-Mar-17	14.64	14.69	14.62	14.67
15-Mar-17	14.52	14.64	14.52	14.64
15-Mar-17	0.069 Dividend
14-Mar-17	14.55	14.58	14.54	14.57
13-Mar-17	14.55	14.58	14.54	14.54
10-Mar-17	14.54	14.58	14.5	14.55
9-Mar-17	14.56	14.58	14.49	14.53
8-Mar-17	14.67	14.68	14.58	14.59
7-Mar-17	14.75	14.76	14.71	14.72
6-Mar-17	14.74	14.75	14.7	14.75
3-Mar-17	14.71	14.75	14.7	14.74
2-Mar-17	14.72	14.74	14.71	14.72
1-Mar-17	14.75	14.75	14.7	14.74
28-Feb-17	14.8	14.8	14.76	14.78
27-Feb-17	14.8	14.82	14.77	14.79
24-Feb-17	14.77	14.79	14.75	14.78
23-Feb-17	14.69	14.76	14.68	14.76
22-Feb-17	14.66	14.71	14.66	14.68
21-Feb-17	14.68	14.71	14.65	14.68
17-Feb-17	14.71	14.72	14.66	14.66
16-Feb-17	14.7	14.73	14.67	14.68
15-Feb-17	14.66	14.73	14.65	14.72
15-Feb-17	0.07 Dividend
14-Feb-17	14.78	14.79	14.71	14.78
13-Feb-17	14.77	14.79	14.73	14.77
10-Feb-17	14.76	14.77	14.7	14.75
9-Feb-17	14.76	14.78	14.71	14.75
8-Feb-17	14.71	14.78	14.7	14.75
7-Feb-17	14.69	14.71	14.67	14.71
6-Feb-17	14.64	14.71	14.63	14.68
3-Feb-17	14.61	14.69	14.61	14.63
2-Feb-17	14.56	14.62	14.55	14.6
1-Feb-17	14.58	14.63	14.52	14.56
31-Jan-17	14.56	14.58	14.54	14.57
30-Jan-17	14.54	14.57	14.51	14.54
27-Jan-17	14.53	14.56	14.52	14.54
26-Jan-17	14.49	14.55	14.48	14.51
25-Jan-17	14.51	14.52	14.47	14.49
24-Jan-17	14.52	14.58	14.52	14.54
23-Jan-17	14.38	14.56	14.38	14.54
20-Jan-17	14.37	14.45	14.34	14.43
19-Jan-17	14.5	14.52	14.35	14.37
18-Jan-17	14.52	14.55	14.5	14.5
17-Jan-17	14.55	14.58	14.51	14.52
13-Jan-17	14.56	14.58	14.51	14.55
13-Jan-17	0.07002 Dividend
12-Jan-17	14.67	14.67	14.6	14.64
11-Jan-17	14.55	14.65	14.53	14.65
10-Jan-17	14.53	14.57	14.51	14.55
9-Jan-17	14.52	14.56	14.46	14.51
6-Jan-17	14.5	14.55	14.47	14.51
5-Jan-17	14.54	14.57	14.43	14.5
4-Jan-17	14.33	14.52	14.32	14.52
3-Jan-17	14.22	14.35	14.22	14.3
30-Dec-16	14.24	14.26	14.21	14.23
29-Dec-16	14.21	14.25	14.21	14.23
28-Dec-16	14.21	14.23	14.17	14.21
27-Dec-16	14.25	14.27	14.19	14.23
23-Dec-16	14.25	14.28	14.24	14.25
22-Dec-16	14.21	14.29	14.21	14.23
21-Dec-16	14.13	14.25	14.13	14.23
20-Dec-16	14.11	14.17	14.1	14.14
19-Dec-16	14.09	14.13	14.07	14.12
16-Dec-16	14.06	14.1	14.04	14.06
15-Dec-16	14	14.09	13.96	14.04
15-Dec-16	0.073 Dividend
14-Dec-16	14.14	14.15	14.09	14.09
13-Dec-16	14.14	14.17	14.11	14.13
12-Dec-16	14.15	14.18	14.11	14.11
9-Dec-16	14.2	14.24	14.18	14.19
8-Dec-16	14.21	14.28	14.2	14.21
7-Dec-16	14.24	14.28	14.22	14.23
6-Dec-16	14.17	14.24	14.17	14.2
5-Dec-16	14.16	14.23	14.15	14.18
2-Dec-16	14.18	14.23	14.16	14.18
1-Dec-16	14.21	14.25	14.13	14.17
30-Nov-16	14.32	14.36	14.26	14.27
29-Nov-16	14.37	14.41	14.35	14.38
28-Nov-16	14.38	14.42	14.34	14.4
25-Nov-16	14.36	14.42	14.36	14.36
23-Nov-16	14.25	14.39	14.25	14.37
22-Nov-16	14.34	14.4	14.31	14.38
21-Nov-16	14.24	14.36	14.24	14.3
18-Nov-16	14.35	14.39	14.22	14.22
17-Nov-16	14.42	14.46	14.33	14.33
16-Nov-16	14.33	14.47	14.31	14.41
15-Nov-16	14.08	14.35	14.08	14.31
15-Nov-16	0.07014 Dividend
14-Nov-16	14.27	14.29	14.09	14.12
11-Nov-16	14.36	14.38	14.26	14.35
10-Nov-16	14.69	14.7	14.38	14.38
9-Nov-16	14.7	14.78	14.68	14.68
8-Nov-16	14.81	14.85	14.79	14.83
7-Nov-16	14.87	14.93	14.81	14.82
4-Nov-16	14.76	14.82	14.75	14.81
3-Nov-16	14.88	14.88	14.76	14.76
2-Nov-16	14.92	14.94	14.82	14.9
1-Nov-16	14.98	14.99	14.9	14.93
31-Oct-16	15.03	15.04	14.98	14.98
28-Oct-16	15.07	15.08	15.01	15.02
27-Oct-16	15.1	15.11	15.04	15.07
26-Oct-16	15.12	15.14	15.1	15.13
25-Oct-16	15.1	15.14	15.08	15.12
24-Oct-16	15.1	15.11	15.08	15.09
21-Oct-16	15.02	15.08	15.01	15.07
20-Oct-16	15.01	15.05	15	15.02
19-Oct-16	14.99	15.06	14.99	15.01
18-Oct-16	14.93	15.04	14.92	14.96
17-Oct-16	14.93	14.95	14.9	14.9
14-Oct-16	14.91	14.96	14.9	14.93
14-Oct-16	0.068 Dividend
13-Oct-16	14.92	14.97	14.85	14.96
12-Oct-16	14.96	14.98	14.92	14.94
11-Oct-16	15.08	15.09	14.97	14.98
10-Oct-16	15.11	15.13	15.08	15.08
7-Oct-16	15.13	15.13	15.08	15.09
6-Oct-16	15.09	15.13	15.07	15.11
5-Oct-16	15.09	15.14	15.08	15.09
4-Oct-16	15.15	15.19	15.09	15.09
3-Oct-16	15.13	15.17	15.11	15.14
30-Sep-16	15.13	15.17	15.07	15.13
29-Sep-16	15.24	15.25	15.07	15.08
28-Sep-16	15.23	15.24	15.22	15.23
27-Sep-16	15.21	15.24	15.17	15.22
26-Sep-16	15.26	15.27	15.21	15.22
23-Sep-16	15.26	15.29	15.23	15.28
22-Sep-16	15.21	15.26	15.19	15.26
21-Sep-16	15.13	15.19	15.1	15.19

<colgroup><col><col><col span="3"><col></colgroup><tbody>
</tbody>

 

[istiasztalos]

Hi,

The most commonly used formula in Excel to look up things based on specific criteria is INDEX, it returns the contents of a cell from a range at a specific position (e.g. Nth cell in a column). That given position is usually determined using a MATCH formula, but here several other options come to play depending on what exactly you need, like MIN, MAX, LARGE, SMALL, etc...

Here's how you can use INDEX to build a monthly summary table in this example. Assume the contents of your date are named as follows.
'dates' = list of dates in first column
'open' = list of open prices, along with the Dividend info
'high' = ilst of high prices
'close' = list of close prices

div day formula, when dividend was paid that month, this is an array formula, enter using CTRL+SHIFT+ENTER
=INDEX(dates,MATCH(1,IF(ISNUMBER(SEARCH("Divid",open)),1,0)*IF(MONTH(dates)=MONTH(I3),1,0),0))

close price ex_div-1 formula, tells close price the day before ex-dividend
=INDEX(close,MATCH(J3-1,dates,0))

first same price formula, tells what is the say that is the lowest in the table, in the same month and price is >= compared to your buy price; this is also an array formula, enter using CTRL+SHIFT+ENTER
=INDEX(dates,MAX(IF(high>=K3,IF(dates>J3,ROW((INDIRECT("1:"&COUNT(dates))))))))

when you have them in row 3, just drag them down

	I	J	K	L
2		div day	close price ex_div-1	first same price
3	01-Aug-17	15/08/2017	15.07	30/08/2017
4	01-Jul-17	14/07/2017	15.13	19/07/2017
5	01-Jun-17	15/06/2017	15.11	26/06/2017

<tbody>
</tbody>

From here you can easily calculate the difference between the two dates, averages, etc....

*edit*
For your self learning, I'd recommend INDEX, MATCH and Array formulas in general, they can be extremely powerful and in my brief experience answering questions here they solve many of the formula related questions