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

Função Históricodeações

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).
TickerAção
MSFTMicrosoft
FBFacebook
AAPLApple
FTCHFarfetch
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çalhosDetalhe
0Diário
1Semanal
2Mensal
[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çalhosDetalhe
0Sem cabeçalhos
1Mostrar cabeçalhos
2Mostrar 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).
PropriedadeDetalhe
0 = DataPrimeiro dia de negociação válido do período
1 = FechoPreço de fecho no último dia de negociação do período
2 = AberturaPreço de abertura no último dia de negociação do período
3 = MáximoO preço mais elevado do dia com o máximo mais alto do período
4 = MínimoO preço mais baixo do dia com o mínimo mais baixo do período
5 = VolumeVolume de ações negociadas durante o período de tempo
A forma mais simples desta função seria:

Forma mais simples da função
=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.

Matriz com mais informação
=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.

Formatos de data

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.

Data HOJE()-1
=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.

Data DATAM(I6;-1)
=DATAM(I6;-1)
E temos assim criada uma fórmula que gera um intervalo dinâmico que se actualiza diariamente.

Fórmula para criar intervalo de dados
=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

Tickers report mensal

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).

Definir a data fim
=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.

Definir a data início
=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.

Indicar informação a apresentar

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 2ª linha
=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 1ª linha
=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.

Ajustar linhas

A largura da coluna vazia.

Ajustar coluna

Selecionar Inserir -> Gráfico Sparkline de Linhas.

Menu sparkline

Selecionar a linha dos dados e inserir o gráfico na célula vazia.

Inserir sparkline

Arrastar o gráfico para as restantes linhas.

Report final

E também é possível formatar os gráficos de linhas Sparkline.

Formatar sparkline

E formatar as células com os tickers para formato Ações.

Converter células

Descargas

Descarrega aqui o ficheiro com os exemplos para usar a Função HISTÓRICODEAÇÕES (Stockhistory) do Excel 365
Artigo anterior Artigo seguinte
Sem comentários
Comentar
url comentário

Pesquisa Google