Operando o IDIV com base em Médias Móveis no Pandas

• Este JNB é uma continuaçao de um estudo feito em um JNB anterior:
  https://rafsz.github.io/Python+Pandas21/MAs_9x21_23mar22_18h.html

• Este JNB tem 4 etapas:

1. Pegando as cotações de uma lista de ações até um dia D e guardando em um Pandas df
2. Calculando a diferença entre os valores das Médias Móveis (MMs) 09x21 e substituindo no df
3. Obtendo as mensagens de BBUY (comprar), STOP(stopar, zerar a posição), Hold (segurar, manter a posição) ou Wait (aguardar, não comprar)
4. Inserindo as mensagens de BUY, STOP, Hold ou WAIT num df e salvando no excel

I. Criando a lista de Códigos das ações, pegando as cotações e guardando em um Pandas df

# importando as Bibliotecas que vamos usar
import yfinance as yf
import pandas as pd

# Neste JNB vamos usar uma lista de acoes tiradas do IDIV - o índice de boas pagadoras de dividendos da B3 
# O arq excel com a composição deste índice pode ser baixado em:  
# https://www.b3.com.br/pt_br/market-data-e-indices/indices/indices-de-segmentos-e-setoriais/indice-dividendos-idiv-composicao-da-carteira.htm
# Na composição do IDIV de 22.mar.22 consta o papel AURE3, que veio substituir a CESP
# https://www.infomoney.com.br/cotacoes/b3/acao/auren-energia-aure3/
# Como o histórico deste papel começa só em 28/03, não temos com calcular a sua média móvel de 21 dias ainda...  Portanto, ele foi retirado da nossa lista.

# O arq CSV baixado do site da B3 foi editado no LibreOffice e salvo como XLSX (Excel)
# Lendo o arq excel simplificado com 3 colunas com a composicao do IDIV e salvando num Pandas df
dftickers = pd.read_excel("B3_IDIV_31-03-22-editadosemAURE3.xlsx")

# visualizando o df
display (dftickers)

	Código	Ação	Part. (%)
0	ABCB4	ABC BRASIL	0.597
1	BRSR6	BANRISUL	1.149
2	BBSE3	BBSEGURIDADE	3.669
3	BBDC3	BRADESCO	3.627
4	BBDC4	BRADESCO	3.736
5	BRAP4	BRADESPAR	4.174
6	BBAS3	BRASIL	3.306
7	AGRO3	BRASILAGRO	0.834
8	CCRO3	CCR SA	2.765
9	CMIG3	CEMIG	2.745
10	CMIG4	CEMIG	3.344
11	CSMG3	COPASA	1.407
12	CPLE3	COPEL	1.077
13	CPLE6	COPEL	2.852
14	CPFE3	CPFL ENERGIA	3.134
15	CYRE3	CYRELA REALT	2.556
16	DIRR3	DIRECIONAL	0.476
17	ELET3	ELETROBRAS	2.827
18	ELET6	ELETROBRAS	3.077
19	ENAT3	ENAUTA PART	0.829
20	ENBR3	ENERGIAS BR	2.719
21	EGIE3	ENGIE BRASIL	2.585
22	ROMI3	INDS ROMI	0.271
23	MYPK3	IOCHP-MAXION	0.902
24	ITSA4	ITAUSA	3.632
25	ITUB3	ITAUUNIBANCO	3.219
26	ITUB4	ITAUUNIBANCO	3.060
27	JHSF3	JHSF PART	0.995
28	MRVE3	MRV	1.947
29	PSSA3	PORTO SEGURO	1.949
30	QUAL3	QUALICORP	2.357
31	SAPR4	SANEPAR	0.425
32	SANB11	SANTANDER BR	4.587
33	CSNA3	SID NACIONAL	2.641
34	SYNE3	SYN PROP TEC	0.117
35	TAEE11	TAESA	4.819
36	TGMA3	TEGMA	0.274
37	VIVT3	TELEF BRASIL	5.948
38	TRPL4	TRAN PAULIST	4.315
39	UNIP6	UNIPAR	2.354
40	WIZS3	WIZ S.A.	0.350

# Vamos inserir uma coluna com os codigos.SA no df inicial (dfidiva) para poder casar com o dicionario e poder importar as mensagens
# https://www.statology.org/pandas-combine-two-columns/
dftickers['codigoSA'] = dftickers['Código'] + '.SA'
display(dftickers)

	Código	Ação	Part. (%)	codigoSA
0	ABCB4	ABC BRASIL	0.597	ABCB4.SA
1	BRSR6	BANRISUL	1.149	BRSR6.SA
2	BBSE3	BBSEGURIDADE	3.669	BBSE3.SA
3	BBDC3	BRADESCO	3.627	BBDC3.SA
4	BBDC4	BRADESCO	3.736	BBDC4.SA
5	BRAP4	BRADESPAR	4.174	BRAP4.SA
6	BBAS3	BRASIL	3.306	BBAS3.SA
7	AGRO3	BRASILAGRO	0.834	AGRO3.SA
8	CCRO3	CCR SA	2.765	CCRO3.SA
9	CMIG3	CEMIG	2.745	CMIG3.SA
10	CMIG4	CEMIG	3.344	CMIG4.SA
11	CSMG3	COPASA	1.407	CSMG3.SA
12	CPLE3	COPEL	1.077	CPLE3.SA
13	CPLE6	COPEL	2.852	CPLE6.SA
14	CPFE3	CPFL ENERGIA	3.134	CPFE3.SA
15	CYRE3	CYRELA REALT	2.556	CYRE3.SA
16	DIRR3	DIRECIONAL	0.476	DIRR3.SA
17	ELET3	ELETROBRAS	2.827	ELET3.SA
18	ELET6	ELETROBRAS	3.077	ELET6.SA
19	ENAT3	ENAUTA PART	0.829	ENAT3.SA
20	ENBR3	ENERGIAS BR	2.719	ENBR3.SA
21	EGIE3	ENGIE BRASIL	2.585	EGIE3.SA
22	ROMI3	INDS ROMI	0.271	ROMI3.SA
23	MYPK3	IOCHP-MAXION	0.902	MYPK3.SA
24	ITSA4	ITAUSA	3.632	ITSA4.SA
25	ITUB3	ITAUUNIBANCO	3.219	ITUB3.SA
26	ITUB4	ITAUUNIBANCO	3.060	ITUB4.SA
27	JHSF3	JHSF PART	0.995	JHSF3.SA
28	MRVE3	MRV	1.947	MRVE3.SA
29	PSSA3	PORTO SEGURO	1.949	PSSA3.SA
30	QUAL3	QUALICORP	2.357	QUAL3.SA
31	SAPR4	SANEPAR	0.425	SAPR4.SA
32	SANB11	SANTANDER BR	4.587	SANB11.SA
33	CSNA3	SID NACIONAL	2.641	CSNA3.SA
34	SYNE3	SYN PROP TEC	0.117	SYNE3.SA
35	TAEE11	TAESA	4.819	TAEE11.SA
36	TGMA3	TEGMA	0.274	TGMA3.SA
37	VIVT3	TELEF BRASIL	5.948	VIVT3.SA
38	TRPL4	TRAN PAULIST	4.315	TRPL4.SA
39	UNIP6	UNIPAR	2.354	UNIP6.SA
40	WIZS3	WIZ S.A.	0.350	WIZS3.SA

# transformando a Coluna de Tickers (códigos de negociação) em uma Lista de Tickers
# https://www.delftstack.com/howto/python-pandas/pandas-column-to-list/
ltickers = dftickers['codigoSA'].tolist()
print(ltickers)
print(len(ltickers))

['ABCB4.SA', 'BRSR6.SA', 'BBSE3.SA', 'BBDC3.SA', 'BBDC4.SA', 'BRAP4.SA', 'BBAS3.SA', 'AGRO3.SA', 'CCRO3.SA', 'CMIG3.SA', 'CMIG4.SA', 'CSMG3.SA', 'CPLE3.SA', 'CPLE6.SA', 'CPFE3.SA', 'CYRE3.SA', 'DIRR3.SA', 'ELET3.SA', 'ELET6.SA', 'ENAT3.SA', 'ENBR3.SA', 'EGIE3.SA', 'ROMI3.SA', 'MYPK3.SA', 'ITSA4.SA', 'ITUB3.SA', 'ITUB4.SA', 'JHSF3.SA', 'MRVE3.SA', 'PSSA3.SA', 'QUAL3.SA', 'SAPR4.SA', 'SANB11.SA', 'CSNA3.SA', 'SYNE3.SA', 'TAEE11.SA', 'TGMA3.SA', 'VIVT3.SA', 'TRPL4.SA', 'UNIP6.SA', 'WIZS3.SA']
41

# Pegando as cotações de Fechamento Ajustado e guardando num df (cotações = quotes em inglês)
# Queremos do início do ano até 6a-feira 01/04, então a data final vai ser o dia seguinte
dfquotes = yf.download(ltickers, start="2022-01-01", end = "2022-04-02")["Adj Close"]
dfquotes.shape

[*********************100%***********************]  41 of 41 completed

(64, 41)

# Verificando se tem algum valor nulo no df
dfquotes.isna().sum()

ABCB4.SA     1
AGRO3.SA     1
BBAS3.SA     1
BBDC3.SA     1
BBDC4.SA     1
BBSE3.SA     1
BRAP4.SA     1
BRSR6.SA     1
CCRO3.SA     1
CMIG3.SA     1
CMIG4.SA     1
CPFE3.SA     1
CPLE3.SA     1
CPLE6.SA     1
CSMG3.SA     1
CSNA3.SA     1
CYRE3.SA     1
DIRR3.SA     1
EGIE3.SA     1
ELET3.SA     1
ELET6.SA     1
ENAT3.SA     1
ENBR3.SA     1
ITSA4.SA     1
ITUB3.SA     1
ITUB4.SA     1
JHSF3.SA     1
MRVE3.SA     1
MYPK3.SA     1
PSSA3.SA     1
QUAL3.SA     1
ROMI3.SA     1
SANB11.SA    1
SAPR4.SA     1
SYNE3.SA     1
TAEE11.SA    1
TGMA3.SA     1
TRPL4.SA     1
UNIP6.SA     1
VIVT3.SA     1
WIZS3.SA     1
dtype: int64

# Verificando quais são as datas que têm valores nulos
nan_values = dfquotes[dfquotes.isna().any(axis=1)]
print (nan_values)

            ABCB4.SA  AGRO3.SA  BBAS3.SA  BBDC3.SA  BBDC4.SA  BBSE3.SA  \
Date                                                                     
2022-03-01       NaN       NaN       NaN       NaN       NaN       NaN   

            BRAP4.SA  BRSR6.SA  CCRO3.SA  CMIG3.SA  ...  ROMI3.SA  SANB11.SA  \
Date                                                ...                        
2022-03-01       NaN       NaN       NaN       NaN  ...       NaN        NaN   

            SAPR4.SA  SYNE3.SA  TAEE11.SA  TGMA3.SA  TRPL4.SA  UNIP6.SA  \
Date                                                                      
2022-03-01       NaN       NaN        NaN       NaN       NaN       NaN   

            VIVT3.SA  WIZS3.SA  
Date                            
2022-03-01       NaN       NaN  

[1 rows x 41 columns]

# O valor com NaN é 01/03/22, 3a de Carnaval
# Vamos eliminar os valores nulos e verificar se o "shape" caiu para (63, 41) (isto é, 63 linhas, 41 colunas, uma para cada ação)
dfquotes = dfquotes.dropna(axis = 0, how ='any')
dfquotes.shape

(63, 41)

II. Calculando a diferença entre os valores das Médias Móveis (MMs) 09x21 e substituindo no df

# Vamos calcular as Médias Móveis (MM) e substituir os valores das cotações pela diferença da MM09 para a MM21
# Esta diferença vai nos indicar se a MM09 está acima  da MM21 (valor positivo) ou abaixo (valor negativo)
for (col) in dfquotes:
    dfquotes[col] = (dfquotes[col].rolling(9).mean()) - (dfquotes[col].rolling(21).mean())

dfquotes.head(30)

	ABCB4.SA	AGRO3.SA	BBAS3.SA	BBDC3.SA	BBDC4.SA	BBSE3.SA	BRAP4.SA	BRSR6.SA	CCRO3.SA	CMIG3.SA	...	ROMI3.SA	SANB11.SA	SAPR4.SA	SYNE3.SA	TAEE11.SA	TGMA3.SA	TRPL4.SA	UNIP6.SA	VIVT3.SA	WIZS3.SA
Date
2022-01-03	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-04	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-05	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-06	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-07	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-10	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-11	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-12	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-13	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-14	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-17	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-18	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-19	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-20	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-21	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-24	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-25	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-26	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-27	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-28	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2022-01-31	0.098807	-0.403601	1.396231	0.547364	0.772363	0.897241	0.490635	0.522625	0.570081	0.278279	...	0.025846	0.651979	0.150635	0.156825	0.880793	0.404903	0.179206	-2.365397	0.785170	0.263735
2022-02-01	0.091950	-0.335911	1.393613	0.568324	0.813098	1.040751	0.240159	0.508018	0.669644	0.350292	...	0.166443	0.654661	0.168730	0.199841	0.923174	0.592938	0.178254	-1.442699	0.801597	0.366274
2022-02-02	0.117041	-0.314393	1.338620	0.582667	0.837751	1.178154	0.064286	0.478189	0.697730	0.378498	...	0.221717	0.688563	0.166667	0.222222	0.880634	0.635567	0.187619	-0.464921	0.844401	0.412077
2022-02-03	0.124210	-0.252979	1.275001	0.616283	0.897502	1.304564	-0.033016	0.429140	0.695205	0.377710	...	0.227789	0.696797	0.172698	0.243333	0.802222	0.641768	0.161428	0.200793	0.927957	0.433648
2022-02-04	0.146963	-0.066644	1.202139	0.641853	0.933680	1.398151	0.030476	0.380706	0.671537	0.367625	...	0.204901	0.761500	0.166508	0.247778	0.683174	0.662850	0.129524	0.837777	0.978817	0.452856
2022-02-07	0.142132	0.114909	1.020524	0.610793	0.892210	1.521051	0.162540	0.282147	0.642662	0.327601	...	0.158970	0.651295	0.160794	0.213175	0.589523	0.624096	0.103809	1.389047	0.935064	0.452708
2022-02-08	0.178133	0.325302	0.900218	0.592241	0.844265	1.617996	0.316825	0.192813	0.577181	0.280643	...	0.115280	0.545893	0.151111	0.159683	0.505555	0.598984	0.059524	2.052698	0.982133	0.442218
2022-02-09	0.167068	0.566028	0.720760	0.473419	0.674297	1.592653	0.533810	0.087796	0.481247	0.208158	...	0.055364	0.288811	0.135714	0.112222	0.448413	0.569066	0.000159	2.806983	1.001245	0.428920
2022-02-10	0.164574	0.792110	0.554241	0.333045	0.482920	1.533570	0.805238	-0.003383	0.402039	0.143867	...	0.096296	0.132797	0.123651	0.069683	0.377778	0.599294	-0.039683	3.521110	0.942804	0.416214
2022-02-11	0.199795	0.918674	0.460737	0.191099	0.290971	1.384164	1.113333	-0.097329	0.292220	-0.023006	...	-0.070619	0.039981	0.102698	0.017936	0.228095	0.494658	-0.089048	4.001427	0.827816	0.347066

30 rows × 41 columns

# Já vimos que temos valores nulos no df
# Provavelmente são as 20 primeiras linhas, para as quais não é possível calcular a MM21
dfquotes.isna().sum()

ABCB4.SA     20
AGRO3.SA     20
BBAS3.SA     20
BBDC3.SA     20
BBDC4.SA     20
BBSE3.SA     20
BRAP4.SA     20
BRSR6.SA     20
CCRO3.SA     20
CMIG3.SA     20
CMIG4.SA     20
CPFE3.SA     20
CPLE3.SA     20
CPLE6.SA     20
CSMG3.SA     20
CSNA3.SA     20
CYRE3.SA     20
DIRR3.SA     20
EGIE3.SA     20
ELET3.SA     20
ELET6.SA     20
ENAT3.SA     20
ENBR3.SA     20
ITSA4.SA     20
ITUB3.SA     20
ITUB4.SA     20
JHSF3.SA     20
MRVE3.SA     20
MYPK3.SA     20
PSSA3.SA     20
QUAL3.SA     20
ROMI3.SA     20
SANB11.SA    20
SAPR4.SA     20
SYNE3.SA     20
TAEE11.SA    20
TGMA3.SA     20
TRPL4.SA     20
UNIP6.SA     20
VIVT3.SA     20
WIZS3.SA     20
dtype: int64

# Eliminando as linhas NA do novo df
dfquotes = dfquotes.dropna(axis = 0, how ='any')
dfquotes.head(10)

	ABCB4.SA	AGRO3.SA	BBAS3.SA	BBDC3.SA	BBDC4.SA	BBSE3.SA	BRAP4.SA	BRSR6.SA	CCRO3.SA	CMIG3.SA	...	ROMI3.SA	SANB11.SA	SAPR4.SA	SYNE3.SA	TAEE11.SA	TGMA3.SA	TRPL4.SA	UNIP6.SA	VIVT3.SA	WIZS3.SA
Date
2022-01-31	0.098807	-0.403601	1.396231	0.547364	0.772363	0.897241	0.490635	0.522625	0.570081	0.278279	...	0.025846	0.651979	0.150635	0.156825	0.880793	0.404903	0.179206	-2.365397	0.785170	0.263735
2022-02-01	0.091950	-0.335911	1.393613	0.568324	0.813098	1.040751	0.240159	0.508018	0.669644	0.350292	...	0.166443	0.654661	0.168730	0.199841	0.923174	0.592938	0.178254	-1.442699	0.801597	0.366274
2022-02-02	0.117041	-0.314393	1.338620	0.582667	0.837751	1.178154	0.064286	0.478189	0.697730	0.378498	...	0.221717	0.688563	0.166667	0.222222	0.880634	0.635567	0.187619	-0.464921	0.844401	0.412077
2022-02-03	0.124210	-0.252979	1.275001	0.616283	0.897502	1.304564	-0.033016	0.429140	0.695205	0.377710	...	0.227789	0.696797	0.172698	0.243333	0.802222	0.641768	0.161428	0.200793	0.927957	0.433648
2022-02-04	0.146963	-0.066644	1.202139	0.641853	0.933680	1.398151	0.030476	0.380706	0.671537	0.367625	...	0.204901	0.761500	0.166508	0.247778	0.683174	0.662850	0.129524	0.837777	0.978817	0.452856
2022-02-07	0.142132	0.114909	1.020524	0.610793	0.892210	1.521051	0.162540	0.282147	0.642662	0.327601	...	0.158970	0.651295	0.160794	0.213175	0.589523	0.624096	0.103809	1.389047	0.935064	0.452708
2022-02-08	0.178133	0.325302	0.900218	0.592241	0.844265	1.617996	0.316825	0.192813	0.577181	0.280643	...	0.115280	0.545893	0.151111	0.159683	0.505555	0.598984	0.059524	2.052698	0.982133	0.442218
2022-02-09	0.167068	0.566028	0.720760	0.473419	0.674297	1.592653	0.533810	0.087796	0.481247	0.208158	...	0.055364	0.288811	0.135714	0.112222	0.448413	0.569066	0.000159	2.806983	1.001245	0.428920
2022-02-10	0.164574	0.792110	0.554241	0.333045	0.482920	1.533570	0.805238	-0.003383	0.402039	0.143867	...	0.096296	0.132797	0.123651	0.069683	0.377778	0.599294	-0.039683	3.521110	0.942804	0.416214
2022-02-11	0.199795	0.918674	0.460737	0.191099	0.290971	1.384164	1.113333	-0.097329	0.292220	-0.023006	...	-0.070619	0.039981	0.102698	0.017936	0.228095	0.494658	-0.089048	4.001427	0.827816	0.347066

10 rows × 41 columns

dfquotes.tail(10)

	ABCB4.SA	AGRO3.SA	BBAS3.SA	BBDC3.SA	BBDC4.SA	BBSE3.SA	BRAP4.SA	BRSR6.SA	CCRO3.SA	CMIG3.SA	...	ROMI3.SA	SANB11.SA	SAPR4.SA	SYNE3.SA	TAEE11.SA	TGMA3.SA	TRPL4.SA	UNIP6.SA	VIVT3.SA	WIZS3.SA
Date
2022-03-21	0.187172	0.440808	-0.178100	0.106031	0.286415	-0.014286	1.762857	0.163944	0.102561	0.452086	...	-0.644569	1.578730	-0.008889	-0.049048	1.121905	0.037204	0.196190	4.051429	1.080141	0.114655
2022-03-22	0.254498	0.330233	-0.131690	0.166313	0.353691	0.130158	1.636031	0.193526	0.169620	0.560184	...	-0.496404	1.823810	-0.017937	-0.029048	1.074285	-0.079678	0.237460	4.271587	1.334361	0.241572
2022-03-23	0.337720	0.179312	-0.013748	0.240417	0.431189	0.253174	1.407143	0.251792	0.261294	0.690342	...	-0.371892	2.090794	-0.013968	0.009365	1.031428	-0.149126	0.326667	4.211111	1.604908	0.339827
2022-03-24	0.397409	-0.010310	0.169089	0.328790	0.525406	0.403333	1.203809	0.302956	0.434069	0.835627	...	-0.174526	2.275397	-0.009683	0.064762	1.099524	-0.080453	0.430000	4.258413	1.820207	0.450344
2022-03-25	0.432163	-0.175426	0.342720	0.413852	0.612568	0.561905	1.066666	0.371238	0.639980	1.001712	...	0.037523	2.379841	-0.000159	0.097302	1.252857	0.036894	0.563968	4.286826	1.967811	0.548007
2022-03-28	0.542502	-0.224588	0.545233	0.494315	0.694116	0.723333	0.929365	0.441032	0.819383	1.163071	...	0.236480	2.489048	0.015714	0.157302	1.396508	0.168193	0.723968	4.116826	2.031355	0.642568
2022-03-29	0.591906	-0.219657	0.655700	0.541959	0.741265	0.855079	0.669364	0.472938	0.914528	1.261310	...	0.418915	2.447302	0.026667	0.233810	1.476508	0.290191	0.820635	3.659524	2.043068	0.708317
2022-03-30	0.603750	-0.323807	0.744267	0.574049	0.776598	0.944286	0.373174	0.490880	0.921155	1.286874	...	0.522029	2.380952	0.034921	0.296190	1.488413	0.429241	0.879365	2.946031	2.002511	0.738901
2022-03-31	0.554970	-0.406590	0.845937	0.592607	0.795808	1.073333	0.089365	0.495737	0.916579	1.329191	...	0.555692	2.263492	0.046032	0.303333	1.484762	0.482566	0.912540	2.311904	1.857835	0.726638
2022-04-01	0.537671	-0.496843	0.869853	0.580367	0.767577	1.190318	-0.236984	0.499903	0.952870	1.359707	...	0.665757	2.097460	0.061429	0.292381	1.476984	0.629057	0.915873	1.562381	1.723420	0.688518

10 rows × 41 columns

# Vamos remover as horas da coluna index do dfidivb, o q vai ser importante para termos um df mais limpo no passo IV abaixo
dfquotes.index = dfquotes.index.date
for idx in dfquotes.index:
    print(idx)

2022-01-31
2022-02-01
2022-02-02
2022-02-03
2022-02-04
2022-02-07
2022-02-08
2022-02-09
2022-02-10
2022-02-11
2022-02-14
2022-02-15
2022-02-16
2022-02-17
2022-02-18
2022-02-21
2022-02-22
2022-02-23
2022-02-24
2022-02-25
2022-03-02
2022-03-03
2022-03-04
2022-03-07
2022-03-08
2022-03-09
2022-03-10
2022-03-11
2022-03-14
2022-03-15
2022-03-16
2022-03-17
2022-03-18
2022-03-21
2022-03-22
2022-03-23
2022-03-24
2022-03-25
2022-03-28
2022-03-29
2022-03-30
2022-03-31
2022-04-01

# Agora que temos o df com a indicação por data se cada papel estava "cruzado pra cima" ou "cruzado pra baixo", podemos obter as mensagens de Compra, Venda, etc.
dfquotes.shape

(43, 41)

III. Obtendo as mensagens de BUY, STOP, Hold ou Wait pra cada papel

# Vamos criar uma funcao similar à "alertacolour" somente para obter as mensagens
def alertasimples (acao):
    if (dfquotes.at[dfquotes.index[a],acao] > 0) and (dfquotes.at[dfquotes.index[b],acao] < 0):
        return('BUY')
    elif (dfquotes.at[dfquotes.index[a],acao] < 0) and (dfquotes.at[dfquotes.index[b],acao] > 0):
        return('STOP!')
    elif (dfquotes.at[dfquotes.index[a],acao] > 0) and (dfquotes.at[dfquotes.index[b],acao] > 0):
        return('hold')
    else:
        return('wait')

# Vamos usar a nova função pra criar um dicionário que casa os codigos com as mensagens para uma data específica
# Para obter a mensagens para o último dia do período (01/04/22, conforme lista de datas acima) fazemos:
# https://www.geeksforgeeks.org/use-get-method-to-create-a-dictionary-in-python-from-a-list-of-elements/?ref=gcse
# Como vamos gerar as mensagens para a última linha (-1), vou chamar este dicionário de dcalert1
dcalert1={}
for papel in ltickers:
    a = -1
    b = a-1
    dcalert1[papel]=alertasimples(papel)
print(dcalert1)

{'ABCB4.SA': 'hold', 'BRSR6.SA': 'hold', 'BBSE3.SA': 'hold', 'BBDC3.SA': 'hold', 'BBDC4.SA': 'hold', 'BRAP4.SA': 'STOP!', 'BBAS3.SA': 'hold', 'AGRO3.SA': 'wait', 'CCRO3.SA': 'hold', 'CMIG3.SA': 'hold', 'CMIG4.SA': 'hold', 'CSMG3.SA': 'hold', 'CPLE3.SA': 'hold', 'CPLE6.SA': 'hold', 'CPFE3.SA': 'hold', 'CYRE3.SA': 'hold', 'DIRR3.SA': 'hold', 'ELET3.SA': 'hold', 'ELET6.SA': 'hold', 'ENAT3.SA': 'hold', 'ENBR3.SA': 'hold', 'EGIE3.SA': 'hold', 'ROMI3.SA': 'hold', 'MYPK3.SA': 'hold', 'ITSA4.SA': 'hold', 'ITUB3.SA': 'hold', 'ITUB4.SA': 'hold', 'JHSF3.SA': 'hold', 'MRVE3.SA': 'hold', 'PSSA3.SA': 'hold', 'QUAL3.SA': 'hold', 'SAPR4.SA': 'hold', 'SANB11.SA': 'hold', 'CSNA3.SA': 'wait', 'SYNE3.SA': 'hold', 'TAEE11.SA': 'hold', 'TGMA3.SA': 'hold', 'VIVT3.SA': 'hold', 'TRPL4.SA': 'hold', 'UNIP6.SA': 'hold', 'WIZS3.SA': 'hold'}

IV. Fazendo um df com todas a mensagens e salvando em xlsx...

# Nesta parte vamos usar o recurso do dicionário para gerar as mensagens para todos os dias do período
# Para tanto, vamos usar o df inicial:
display(dftickers)

	Código	Ação	Part. (%)	codigoSA
0	ABCB4	ABC BRASIL	0.597	ABCB4.SA
1	BRSR6	BANRISUL	1.149	BRSR6.SA
2	BBSE3	BBSEGURIDADE	3.669	BBSE3.SA
3	BBDC3	BRADESCO	3.627	BBDC3.SA
4	BBDC4	BRADESCO	3.736	BBDC4.SA
5	BRAP4	BRADESPAR	4.174	BRAP4.SA
6	BBAS3	BRASIL	3.306	BBAS3.SA
7	AGRO3	BRASILAGRO	0.834	AGRO3.SA
8	CCRO3	CCR SA	2.765	CCRO3.SA
9	CMIG3	CEMIG	2.745	CMIG3.SA
10	CMIG4	CEMIG	3.344	CMIG4.SA
11	CSMG3	COPASA	1.407	CSMG3.SA
12	CPLE3	COPEL	1.077	CPLE3.SA
13	CPLE6	COPEL	2.852	CPLE6.SA
14	CPFE3	CPFL ENERGIA	3.134	CPFE3.SA
15	CYRE3	CYRELA REALT	2.556	CYRE3.SA
16	DIRR3	DIRECIONAL	0.476	DIRR3.SA
17	ELET3	ELETROBRAS	2.827	ELET3.SA
18	ELET6	ELETROBRAS	3.077	ELET6.SA
19	ENAT3	ENAUTA PART	0.829	ENAT3.SA
20	ENBR3	ENERGIAS BR	2.719	ENBR3.SA
21	EGIE3	ENGIE BRASIL	2.585	EGIE3.SA
22	ROMI3	INDS ROMI	0.271	ROMI3.SA
23	MYPK3	IOCHP-MAXION	0.902	MYPK3.SA
24	ITSA4	ITAUSA	3.632	ITSA4.SA
25	ITUB3	ITAUUNIBANCO	3.219	ITUB3.SA
26	ITUB4	ITAUUNIBANCO	3.060	ITUB4.SA
27	JHSF3	JHSF PART	0.995	JHSF3.SA
28	MRVE3	MRV	1.947	MRVE3.SA
29	PSSA3	PORTO SEGURO	1.949	PSSA3.SA
30	QUAL3	QUALICORP	2.357	QUAL3.SA
31	SAPR4	SANEPAR	0.425	SAPR4.SA
32	SANB11	SANTANDER BR	4.587	SANB11.SA
33	CSNA3	SID NACIONAL	2.641	CSNA3.SA
34	SYNE3	SYN PROP TEC	0.117	SYNE3.SA
35	TAEE11	TAESA	4.819	TAEE11.SA
36	TGMA3	TEGMA	0.274	TGMA3.SA
37	VIVT3	TELEF BRASIL	5.948	VIVT3.SA
38	TRPL4	TRAN PAULIST	4.315	TRPL4.SA
39	UNIP6	UNIPAR	2.354	UNIP6.SA
40	WIZS3	WIZ S.A.	0.350	WIZS3.SA

# Vamos criar um novo df que vai receber colunas com as datas e as mensagens
# Ele vai ser uma cópia do df incial, apenas com as colunas relevantes
dfalert = dftickers.copy()
dfalert = dfalert.drop(['Código', 'Part. (%)'], axis = 1)
display(dfalert)

	Ação	codigoSA
0	ABC BRASIL	ABCB4.SA
1	BANRISUL	BRSR6.SA
2	BBSEGURIDADE	BBSE3.SA
3	BRADESCO	BBDC3.SA
4	BRADESCO	BBDC4.SA
5	BRADESPAR	BRAP4.SA
6	BRASIL	BBAS3.SA
7	BRASILAGRO	AGRO3.SA
8	CCR SA	CCRO3.SA
9	CEMIG	CMIG3.SA
10	CEMIG	CMIG4.SA
11	COPASA	CSMG3.SA
12	COPEL	CPLE3.SA
13	COPEL	CPLE6.SA
14	CPFL ENERGIA	CPFE3.SA
15	CYRELA REALT	CYRE3.SA
16	DIRECIONAL	DIRR3.SA
17	ELETROBRAS	ELET3.SA
18	ELETROBRAS	ELET6.SA
19	ENAUTA PART	ENAT3.SA
20	ENERGIAS BR	ENBR3.SA
21	ENGIE BRASIL	EGIE3.SA
22	INDS ROMI	ROMI3.SA
23	IOCHP-MAXION	MYPK3.SA
24	ITAUSA	ITSA4.SA
25	ITAUUNIBANCO	ITUB3.SA
26	ITAUUNIBANCO	ITUB4.SA
27	JHSF PART	JHSF3.SA
28	MRV	MRVE3.SA
29	PORTO SEGURO	PSSA3.SA
30	QUALICORP	QUAL3.SA
31	SANEPAR	SAPR4.SA
32	SANTANDER BR	SANB11.SA
33	SID NACIONAL	CSNA3.SA
34	SYN PROP TEC	SYNE3.SA
35	TAESA	TAEE11.SA
36	TEGMA	TGMA3.SA
37	TELEF BRASIL	VIVT3.SA
38	TRAN PAULIST	TRPL4.SA
39	UNIPAR	UNIP6.SA
40	WIZ S.A.	WIZS3.SA

dfalert.shape

(41, 2)

dfquotes.shape

(43, 41)

print(len(dfquotes.index))

43

# então temos 41 papéis e 43 dias com mensagens (alertas) de compra, stop, venda, aguardar...
for a in range (43):
	dctemp = {}
	for papel in ltickers:
		b=a-1
		dctemp[papel]=alertasimples(papel)
	dfalert[dfquotes.index[a]] = dctemp.values()
display(dfalert)

	Ação	codigoSA	2022-01-31	2022-02-01	2022-02-02	2022-02-03	2022-02-04	2022-02-07	2022-02-08	2022-02-09	...	2022-03-21	2022-03-22	2022-03-23	2022-03-24	2022-03-25	2022-03-28	2022-03-29	2022-03-30	2022-03-31	2022-04-01
0	ABC BRASIL	ABCB4.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
1	BANRISUL	BRSR6.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
2	BBSEGURIDADE	BBSE3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	BUY	hold	hold	hold	hold	hold	hold	hold	hold
3	BRADESCO	BBDC3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	BUY	hold	hold	hold	hold	hold	hold	hold	hold	hold
4	BRADESCO	BBDC4.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
5	BRADESPAR	BRAP4.SA	BUY	hold	hold	STOP!	BUY	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	STOP!
6	BRASIL	BBAS3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	wait	wait	BUY	hold	hold	hold	hold	hold	hold
7	BRASILAGRO	AGRO3.SA	wait	wait	wait	wait	wait	BUY	hold	hold	...	hold	hold	hold	STOP!	wait	wait	wait	wait	wait	wait
8	CCR SA	CCRO3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	BUY	hold	hold	hold	hold	hold	hold	hold	hold	hold
9	CEMIG	CMIG3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
10	CEMIG	CMIG4.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
11	COPASA	CSMG3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	wait	BUY	hold	hold	hold	hold	hold	hold	hold
12	COPEL	CPLE3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
13	COPEL	CPLE6.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
14	CPFL ENERGIA	CPFE3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
15	CYRELA REALT	CYRE3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	BUY	hold	hold	hold	hold	hold	hold	hold	hold
16	DIRECIONAL	DIRR3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	wait	wait	wait	wait	wait	wait	BUY	hold	hold
17	ELETROBRAS	ELET3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	BUY	hold	hold	hold	hold	hold	hold	hold	hold
18	ELETROBRAS	ELET6.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	BUY	hold	hold	hold	hold	hold	hold	hold	hold
19	ENAUTA PART	ENAT3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
20	ENERGIAS BR	ENBR3.SA	hold	hold	hold	hold	hold	hold	hold	STOP!	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
21	ENGIE BRASIL	EGIE3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
22	INDS ROMI	ROMI3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	wait	wait	wait	BUY	hold	hold	hold	hold	hold
23	IOCHP-MAXION	MYPK3.SA	hold	hold	hold	hold	hold	STOP!	wait	wait	...	wait	wait	wait	BUY	hold	hold	hold	hold	hold	hold
24	ITAUSA	ITSA4.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	BUY	hold	hold	hold	hold	hold	hold	hold	hold	hold
25	ITAUUNIBANCO	ITUB3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	BUY	hold	hold	hold	hold	hold	hold	hold	hold	hold
26	ITAUUNIBANCO	ITUB4.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	BUY	hold	hold	hold	hold	hold	hold	hold	hold	hold
27	JHSF PART	JHSF3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	BUY	hold	hold	hold	hold	hold	hold	hold	hold
28	MRV	MRVE3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	wait	wait	wait	wait	BUY	hold	hold	hold	hold
29	PORTO SEGURO	PSSA3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	wait	wait	wait	wait	BUY	hold	hold	hold	hold
30	QUALICORP	QUAL3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	BUY	hold	hold	hold	hold	hold	hold	hold	hold	hold
31	SANEPAR	SAPR4.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	wait	wait	wait	wait	BUY	hold	hold	hold	hold
32	SANTANDER BR	SANB11.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
33	SID NACIONAL	CSNA3.SA	BUY	hold	hold	hold	hold	hold	hold	hold	...	wait	wait	wait	wait	wait	wait	wait	wait	wait	wait
34	SYN PROP TEC	SYNE3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	wait	wait	BUY	hold	hold	hold	hold	hold	hold	hold
35	TAESA	TAEE11.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
36	TEGMA	TGMA3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	STOP!	wait	wait	BUY	hold	hold	hold	hold	hold
37	TELEF BRASIL	VIVT3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
38	TRAN PAULIST	TRPL4.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
39	UNIPAR	UNIP6.SA	STOP!	wait	wait	BUY	hold	hold	hold	hold	...	hold	hold	hold	hold	hold	hold	hold	hold	hold	hold
40	WIZ S.A.	WIZS3.SA	hold	hold	hold	hold	hold	hold	hold	hold	...	BUY	hold	hold	hold	hold	hold	hold	hold	hold	hold

41 rows × 45 columns

# Se quisermos verificar as mensagens, podemos ver as mensagens em um novo dicionário
# Por exemplo, no dfalert temos a mensagem STOP! para AGRO3 em 24/03/22.  Esta data é a coluna -7, contando da última, logo:
dcalert7={}
for papel in ltickers:
    a = -7
    b = a-1
    dcalert7[papel]=alertasimples(papel)
print(dcalert7)

{'ABCB4.SA': 'hold', 'BRSR6.SA': 'hold', 'BBSE3.SA': 'hold', 'BBDC3.SA': 'hold', 'BBDC4.SA': 'hold', 'BRAP4.SA': 'hold', 'BBAS3.SA': 'BUY', 'AGRO3.SA': 'STOP!', 'CCRO3.SA': 'hold', 'CMIG3.SA': 'hold', 'CMIG4.SA': 'hold', 'CSMG3.SA': 'hold', 'CPLE3.SA': 'hold', 'CPLE6.SA': 'hold', 'CPFE3.SA': 'hold', 'CYRE3.SA': 'hold', 'DIRR3.SA': 'wait', 'ELET3.SA': 'hold', 'ELET6.SA': 'hold', 'ENAT3.SA': 'hold', 'ENBR3.SA': 'hold', 'EGIE3.SA': 'hold', 'ROMI3.SA': 'wait', 'MYPK3.SA': 'BUY', 'ITSA4.SA': 'hold', 'ITUB3.SA': 'hold', 'ITUB4.SA': 'hold', 'JHSF3.SA': 'hold', 'MRVE3.SA': 'wait', 'PSSA3.SA': 'wait', 'QUAL3.SA': 'hold', 'SAPR4.SA': 'wait', 'SANB11.SA': 'hold', 'CSNA3.SA': 'wait', 'SYNE3.SA': 'hold', 'TAEE11.SA': 'hold', 'TGMA3.SA': 'wait', 'VIVT3.SA': 'hold', 'TRPL4.SA': 'hold', 'UNIP6.SA': 'hold', 'WIZS3.SA': 'hold'}

# Salvando o dfalert no excel como um novo arq...
writer = pd.ExcelWriter('IDIV_MM09x21_de_2022-02-01_a_04-01.xlsx', engine='xlsxwriter')
dfalert.to_excel(writer, sheet_name='MM09x21_01fev_a_01abr')
writer.save()

Visualizando no LibreOffice:

fim 🐍

• Referências complementares:

Composição do IDIV https://www.b3.com.br/pt_br/market-data-e-indices/indices/indices-de-segmentos-e-setoriais/indice-dividendos-idiv-composicao-da-carteira.htm

Canal Youtube: Código Quant - Finanças Quantitativas PYTHON PARA INVESTIMENTOS #2: Definindo intervalos, calculando e plotando médias móveis
https://youtu.be/BBomKv3NFNc?list=PLmQ5Q79miLmxDc16motuYuG1hLjW0T4Wo

Inserir uma nova coluna num df e copiar os valores de um dicionario pra esta coluna, contanto que as chaves do dicionario batam com alguma coluna do df:
https://www.geeksforgeeks.org/use-get-method-to-create-a-dictionary-in-python-from-a-list-of-elements/?ref=gcse

Para consulta geral:
https://betterprogramming.pub/9-pandas-functions-that-will-do-99-of-any-analytics-task-e6b6fb1b16bf

The JNB that generated this HTML can be found here:
https://github.com/Rafsz/rafsz.github.io/tree/main/Python%2BPandas21