Calc: Corrigir considerando datas

[ElderMarco]

Olá Senhores,

Eu tenho a seguinte tabela de dados:

      DATA	  PRECO
14/01/2014	  15,24
15/03/2014	  16,52
15/06/2014	  17,58
29/07/2014	  18,56
15/07/2015	  11,52

Essa tabela representa a data em que comprei uma certa ação X de uma empresa e por qual valor. E a seguinte tabela é uma tabela de correção desses valores:

      DATA      CORREÇÃO
20/02/2014	      0,9791
15/05/2014	      0,9985
06/06/2014	      0,2000
29/08/2014	      0,9878
15/05/2015	      2,0000

Essa tabela representa as correções que devem ser feitas aos preços de compra devido a eventos ocorridos com a ação, como o pagamento de dividendos, juros sobre capital próprio, desdobramentos, agrupamentos e etc. A segunda coluna é o valor no qual eu devo multiplicar o preço para que a correção possa ser considerada como realizada.

O problema:

O problema é que essa correção deve considerar a data em que o evento ocorreu e as correções só podem ser feitas se a compra ocorreu até a data de correção. Compras posteriores não são corrigidas porque essas correções já estão embutidas no preço da ação.

Por exemplo, para o evento que ocorreu na data 06/06/2014 na última tabela, eu tenho que aplicar ele a todas as compras que ocorreram até essa data e não se aplica as compras que ocorram depois. Assim, eu somente iria multiplicar os preços R$ 15,24 e R$ 16,52 por esse valor. Seguindo essa lógica, a primeira compra, no dia 14/01/2014 seria multiplicada por todos os fatores de correção, mas a segunda já seria multiplicada somente do segundo pra frente, no exemplo dessa tabela. O resultado final seria a tabela abaixo:

      DATA	  PRECO   CORRIGIDO
14/01/2014	  15,24        5,89     (=15.24*0.9791*0.9985*0.2*0.9878*2)
15/03/2014	  16,52        6,52     (=16.52*0.9985*0.2*0.9878*2)
15/06/2014	  17,58       34,73     (=17.58*0.9878*2)
29/07/2014	  18,56       36,67     (=18.56*0.9878*2)
15/07/2015	  11,52       11,52     (=11.52 porque foi comprado depois de os eventos terem ocorrido)

E foi aí que eu não consegui prosseguir. Não consegui escrever uma fórmula que faça essas correções automaticamente, a partir desses dados.

Os senhores teriam alguma pista/dica em relação a isso? Já se depararam com um problema semelhante?

[ElderMarco]

Vou responder a minha própria pergunta com uma pequena macro que escrevi para resolver esse problema. Nada sofisticado, mas pode ser melhorado.

Function FatorCorrecaoPreco (data_compra As Date, tabela_correcao As Variant)

	' Procura por todos os eventos cuja data ex seja posterior a data de
	' compra e monta o fator de correção a partir disso.
	FatorCorrecaoPreco = 1.0
	For i = 1 To ubound (tabela_correcao)
		If data_compra < tabela_correcao (i, 1) Then
			FatorCorrecaoPreco = FatorCorrecaoPreco*tabela_correcao (i, 2)
		End If
	Next i
End Function

Onde os argumentos são a data de compra, um único valor. E um array com a tabela de correção, como a que coloquei na pergunta (primeira coluna com a data a partir da qual se aplica a correção e a segunda com a correção. Daí, por exemplo, para calcular o preço corrigido para o primeiro caso (14/01/2014 , com preço de R$ 15,24), poderíamos fazer algo do tipo

=B1*FATORCORRECAOPRECO(A1; $C$1:$D$5)

Em que B1 = R$ 15,24 e o intervalo C1:D5 corresponde a tabela de correção.

Fica então a dica para o caso de alguém precisar de algo do tipo e cair nesse tópico.

[Grafeno]

Elder, parabéns pela solução e por disponibiliza-la aqui.

[schiavinatto]

Muito bem, Elder, estou arquivando para uso futuro.

[schiavinatto]

Elder, segue uma solução para o problema acima, porém, caso a tabela PREÇO ou CORREÇÃO for ampliada a fórmula deverá ser arrumada/ampliada.

Considerando a tabela PREÇO começando em A1 e a tabela CORREÇÃO começando em A13, a fórmula para o corrigido em 14/01/14 será: =B2*SE($A2<A$14;B$14;1)*SE($A2<A$15;B$15;1)*SE($A2<A$16;B$16;1)*SE($A2<A$17;B$17;1)*SE($A2<A$18;B$18;1). Copiando para as células de baixo da tabelo CORRIGIDO, o resultado dá certo.

Quanto a solução com macro, é provável que seria melhor, porém sou péssimo em macros.

---

Caso a resposta te serviu, por gentileza, click na bolinha a esquerda da resposta, para finalizar a pergunta.

[ElderMarco]

Olá Gilberto,

Obrigado pela resposta. Tanto a tabela de preço quanto a tabela de correção tem o problema de serem dinâmicas, isto é, novos valores vão sendo adicionados a elas. Eu havia pensado em algo do tipo que você fez, mas pensando se existiria alguma maneira de estender automaticamente essas fórmulas de modo a considerar os novos valores adicionados. Também pensei que o LibreOffice poderia ter alguma função financeira do tipo que eu não me dei conta que exista, mas eu acho que não.