Função HISTÓRICODEAÇÕES (Stockhistory) do Excel 365

A Função HISTÓRICODEAÇÕES (Stockhistory) obtém dados históricos do mercado de capitais, carregando-os como uma matriz de dados.

Resumo

A Função HISTÓRICODEAÇÕES obtém dados históricos do mercado de capitais, carregando-os como uma matriz de dados diários, semanais ou mensais de ações que permite construir alguns reports repletos de informação.

Nota: Esta função requer a subscrição do Microsoft 365.

Sintaxe

=HISTÓRICODEAÇÕES(ação; data_início; [data_fim]; [intervalo]; [cabeçalhos]; [propriedade0]; [propriedade1]; [propriedade2]; [propriedade3]; [propriedade4]; [propriedade5])

Argumentos

ação // Sigla de ticker entre aspas ou a referência a uma célula com esta informação. No quado abaixo, exemplos de Tickers de ações (que irão servir para construirmos um report).

Ticker	Ação
MSFT	Microsoft
FB	Facebook
AAPL	Apple
FTCH	Farfetch

data_início // A data mais antiga para obter os dados. Se o intervalo não for 0 (diário), o primeiro ponto de dados pode ser diferente da data_início indicada, ou seja, será a primeira data do período de tempo solicitado.
[Opcional] data_final // A data mais recente para a qual serão obtidos os dados.
[Opcional] intervalo // Especifica o intervalo de cada valor de dados. A predefinição é 0.

Cabeçalhos	Detalhe
0	Diário
1	Semanal
2	Mensal

[Opcional] cabeçalhos // Se queremos ou não apresentar cabeçalhos. O valor predefinido é 1. Os cabeçalhos são linhas de texto que fazem parte da matriz devolvida pela função.

Cabeçalhos	Detalhe
0	Sem cabeçalhos
1	Mostrar cabeçalhos
2	Mostrar identificador e cabeçalhos de instrumento

[Opcional] propriedade0 - propriedade5 // Colunas são obtidas para cada ação: 0 = Data, 1 = Fecho, 2 = Abertura, 3 = Máximo, 4 = Mínimo e 5 = Volume. Se alguma propriedade estiver presente, apenas as colunas indicadas são devolvidas pela ordem apresentada. Os valores predefinidos são 0, 1 (Data e Fecho).

Propriedade	Detalhe
0 = Data	Primeiro dia de negociação válido do período
1 = Fecho	Preço de fecho no último dia de negociação do período
2 = Abertura	Preço de abertura no último dia de negociação do período
3 = Máximo	O preço mais elevado do dia com o máximo mais alto do período
4 = Mínimo	O preço mais baixo do dia com o mínimo mais baixo do período
5 = Volume	Volume de ações negociadas durante o período de tempo

A forma mais simples desta função seria:

=HISTÓRICODEAÇÕES("FTCH";"1/6/2021")

A matriz devolve a data e o fecho do ticker que selecionámos (neste caso FTCH).

Completar a matriz com mais dados

Continuando com o exemplo vamos aumentar o nível de informação que a matriz devolve sobre a ação escolhida.

=HISTÓRICODEAÇÕES("FTCH";"1/6/2021";"10/6/2021";0;1;0;1;4;3;5)

=HISTÓRICODEAÇÕES("FTCH"; // Ticker da Farfetch
"1/6/2021";"10/6/2021"; // Data de início e data final
;0;1;0;1;4;3;5) // Diário; Com cabeçalhos; Data; Preço de Fecho; Mínimo; Máximo; Volume

A ordem dos cabeçalhos pode ser a que considerarmos mais pertinente. No nosso exemplo decidimos colocar o mínimo antes do máximo apesar da posição natural ser a inversa.

Utilização de Datas

Usar datas hardcoded directamente neste função pode ser um problema, já que as opções de formato de data podem variar em diferentes computadores/instalações de Excel devolvendo erros na função.

Se a Função der erro com formatos diferentes de datas, podemos contornar esse erro inserindo datas via referência em outras células ou usando a Função DATA (Date).

Exemplo de data via referência de Função 2

=HISTÓRICODEAÇÕES("FTCH";DATA(2020;12;15)

Exemplo de data via referência de Função 1

=HISTÓRICODEAÇÕES("FTCH";DATA(H11;H12;H13)

Definir um intervalo de dados com Funções de Data e Hora

Usando as funções de Data e Hora do Excel podemos construir intervalos à medida do que queremos e de forma automática. Vamos construir um exemplo em que iremos construir um intervalo de dados do último mês móvel. Ou seja, 1 mês de dados a partir do dia anterior ao dia corrente.

Começamos por definir a data Fim, sabemos que queremos que seja sempre o dia de ontem. Usamos a Função HOJE (Today) e subtraímos 1 dia.

=HOJE()-1

Tendo já definido a data final, sabemos que queremos que a data Início seja sempre 1 mês antes. Para isso, usamos a Função DATAM (Edate) em que definimos que a data de referência é a data Fim (que já sabemos qual é) e simplesmente retiramos-lhe 1 mês.

=DATAM(I6;-1)

E temos assim criada uma fórmula que gera um intervalo dinâmico que se actualiza diariamente.

=HISTÓRICODEAÇÕES("FTCH";K6;I6;0;1;0;1;4;3;5)

Construir um Report de ações

Vamos criar um report mensal para um determinado período móvel em que assumimos que a data Fim será sempre o dia de corrente.

1. Começamos por usar os 4 tickers que identificamos no início do artigo

2. Definimos o período dos dados e a informação que queremos apresentar

Começamos pela data fim, que queremos que seja o dia em que nos encontramos e por isso vamos usar a Função HOJE (Today).

=HOJE()

Para definir a data de início vamos inserir quantos meses de histórico queremos ir buscar, vamos seguir com 12 meses. Tendo esse valor definido, vamos usar a Função DATAM (Edate) à qual iremos subtrair o número de meses que definimos na célula D1.

=DATAM(H1;-D1)

Por fim, vamos escolher a informação que queremos recolher via Função HISTÓRICODEAÇÕES (Stockhistory). Vamos seguir com "1", ou seja os dados de fecho das ações que escolhemos.

3. Preparar o quadro de dados do Report e a linha da data

Começamos por tratar todos os tickers da 2ª linha para baixo com a mesma fórmula e usando a Função TRANSPOR (Transpose) para mudar a orientação dos dados de vertical para horizontal.

=TRANSPOR(HISTÓRICODEAÇÕES($B7;$F$1;$H$1;2;0;$K$1))

=TRANSPOR( // Função TRANSPOR (Transpose) para colocar a informação na horizontal
HISTÓRICODEAÇÕES($B7; // Célula onde se encontra o ticker
$F$1; // Data Início
$H$1; // Data Fim
2; // Dados em formato Mensal
0 // Informação sem cabeçalhos
;$K$1)) // Dados de Fecho da ação

Para o primeiro ticker temos que alterar um pouco a fórmula já que queremos, além dos dados do ticker, acrescentar um cabeçalho que inclua a data. Vamos posicionar a Função na linha acima do Ticker e além dos dados da cotação acrescentamos a propriedade 0 que corresponde ao campo data.

=TRANSPOR(HISTÓRICODEAÇÕES($B6;$F$1;$H$1;2;0;0;$K$1))

1;2;0;$K$1)) // Mensal; Sem cabeçalhos; Data; Dados de fecho da ação

4. Visualizar os dados com Sparklines

Deixamos uma coluna vazia propositadamente para mostrar que com os dados da Função HISTÓRICODEAÇÕES (Stockhistory) podemos usar uma sparkline para visualizar a evolução macro das cotações.

Começamos por ajustar a altura das linhas.