Moving Average Este exemplo ensina como calcular a média móvel de uma série de tempo no Excel. Um avanço em movimento é usado para suavizar irregularidades (picos e vales) para reconhecer facilmente as tendências. 1. Primeiro, vamos dar uma olhada em nossa série de tempo. 2. No separador Dados, clique em Análise de dados. Observação: não é possível encontrar o botão Análise de dados Clique aqui para carregar o suplemento do Analysis ToolPak. 3. Selecione Média móvel e clique em OK. 4. Clique na caixa Input Range e selecione o intervalo B2: M2. 5. Clique na caixa Intervalo e escreva 6. 6. Clique na caixa Output Range e seleccione a célula B3. 8. Faça um gráfico destes valores. Explicação: porque definimos o intervalo como 6, a média móvel é a média dos 5 pontos de dados anteriores eo ponto de dados atual. Como resultado, os picos e vales são suavizados. O gráfico mostra uma tendência crescente. O Excel não consegue calcular a média móvel para os primeiros 5 pontos de dados porque não existem pontos de dados anteriores suficientes. 9. Repita os passos 2 a 8 para intervalo 2 e intervalo 4. Conclusão: Quanto maior o intervalo, mais os picos e vales são suavizados. Quanto menor o intervalo, mais próximas as médias móveis são para os pontos de dados reais. Neste curto tutorial, você aprenderá como calcular rapidamente uma média móvel simples no Excel, quais funções usar para obter a média móvel para os últimos N dias, semanas , Meses ou anos e como adicionar uma linha de tendência de média móvel para um gráfico do Excel. Em alguns artigos recentes, nós demos uma olhada no cálculo da média no Excel. Se você está seguindo nosso blog, você já sabe como calcular uma média normal e quais funções usar para encontrar a média ponderada. No tutorial de hoje, vamos discutir duas técnicas básicas para calcular a média móvel no Excel. O que é a média móvel De um modo geral, a média móvel (também referida como média móvel, média móvel ou média móvel) pode ser definida como uma série de médias para diferentes subconjuntos do mesmo conjunto de dados. É freqüentemente usado em estatísticas, previsões econômicas e meteorológicas ajustadas sazonalmente para entender as tendências subjacentes. Na negociação de ações, média móvel é um indicador que mostra o valor médio de um título ao longo de um determinado período de tempo. Nos negócios, é uma prática comum para calcular uma média móvel de vendas para os últimos 3 meses para determinar a tendência recente. Por exemplo, a média móvel das temperaturas de três meses pode ser calculada tomando a média das temperaturas de janeiro a março, depois a média das temperaturas de fevereiro a abril, depois de março a maio, e assim por diante. Existem diferentes tipos de média móvel, como simples (também conhecido como aritmética), exponencial, variável, triangular e ponderada. Neste tutorial, estaremos analisando a média móvel mais comumente utilizada. Calculando a média móvel simples no Excel No geral, existem duas maneiras de obter uma média móvel simples no Excel - usando fórmulas e opções de linha de tendência. Os exemplos seguintes demonstram ambas as técnicas. Exemplo 1. Calcular a média móvel para um determinado período de tempo Uma média móvel simples pode ser calculada em nenhum momento com a função MÉDIA. Suponha que você tenha uma lista de temperaturas médias mensais na coluna B e queira encontrar uma média móvel de 3 meses (como mostrado na imagem acima). Escreva uma fórmula média usual para os primeiros 3 valores e insira-a na linha correspondente ao 3º valor da parte superior (célula C4 neste exemplo) e, em seguida, copie a fórmula para outras células da coluna: Coluna com uma referência absoluta (como B2) se você desejar, mas não se esqueça de usar referências de linha relativa (sem o sinal) para que a fórmula ajusta corretamente para outras células. Lembrando que uma média é calculada adicionando valores e dividindo a soma pelo número de valores a serem calculados, você pode verificar o resultado usando a fórmula SUM: Exemplo 2. Obter média móvel para os últimos N dias / semanas / Meses / anos em uma coluna Supondo que você tenha uma lista de dados, por exemplo Venda ou cotações de ações, e você quer saber a média dos últimos 3 meses em qualquer ponto do tempo. Para isso, você precisa de uma fórmula que recalcule a média assim que você digitar um valor para o próximo mês. Qual função do Excel é capaz de fazer isso O bom AVERAGE antigo em combinação com OFFSET e COUNT. MÉDIA (OFFSET (NÚMERO DE COUNT (NÚMERO COMPLETO - N, 0, N, 1)) Onde N é o número dos últimos dias / semanas / meses / anos a incluir na média. Não sei como usar essa fórmula de média móvel em planilhas do Excel O exemplo a seguir tornará as coisas mais claras. Supondo que os valores para a média estão na coluna B começando na linha 2, a fórmula seria a seguinte: E agora, vamos tentar entender o que esta fórmula de média móvel Excel está realmente fazendo. A COUNT função COUNT (B2: B100) conta quantos valores já estão inseridos na coluna B. Começamos a contar em B2 porque a linha 1 é o cabeçalho da coluna. A função OFFSET leva a célula B2 (o primeiro argumento) como ponto de partida e desloca a contagem (o valor retornado pela função COUNT) movendo 3 linhas para cima (-3 no 2º argumento). Como resultado, retorna a soma de valores em um intervalo composto por 3 linhas (3 no 4 º argumento) e 1 coluna (1 no último argumento), que é o mais tardar 3 meses que queremos. Finalmente, a soma retornada é passada para a função MÉDIA para calcular a média móvel. Dica. Se estiver trabalhando com planilhas continuamente atualizáveis onde novas linhas provavelmente serão adicionadas no futuro, forneça um número suficiente de linhas à função COUNT para acomodar novas entradas possíveis. Não é um problema se você incluir mais linhas do que realmente necessário contanto que você tenha a primeira célula direita, a função COUNT irá descartar todas as linhas vazias de qualquer maneira. Como você provavelmente notou, a tabela neste exemplo contém dados para apenas 12 meses, e ainda o intervalo B2: B100 é fornecido para COUNT, apenas para estar no lado de salvar :) Exemplo 3. Obter média móvel para os últimos N valores em Uma linha Se você deseja calcular uma média móvel para os últimos N dias, meses, anos, etc. na mesma linha, você pode ajustar a fórmula Offset desta maneira: Supondo que B2 é o primeiro número na linha e você quer Para incluir os últimos 3 números na média, a fórmula tem a seguinte forma: Criando um gráfico de média móvel do Excel Se você já criou um gráfico para seus dados, adicionar uma linha de tendência de média móvel para esse gráfico é uma questão de segundos. Para isso, vamos usar o recurso Excel Trendline e seguir as etapas detalhadas abaixo. Para este exemplo, criei um gráfico de colunas em 2D (grupo Inserir guia gt Gráficos) para nossos dados de vendas: E agora, queremos visualizar a média móvel por 3 meses. No Excel 2010 e no Excel 2007, vá para Layout gt Trendline gt Mais Opções da Trendline. Dica. Se você não precisa especificar os detalhes, como o intervalo de média móvel ou os nomes, você pode clicar em Design gt Adicionar elemento gráfico gt Trendline gt Média móvel para o resultado imediato. O painel Format Trendline será aberto no lado direito da planilha no Excel 2017 e a caixa de diálogo correspondente aparecerá no Excel 2010 e 2007. Para refinar o bate-papo, você pode alternar para a linha Fill amp ou os efeitos na guia O painel Format Trendline e jogar com diferentes opções, como tipo de linha, cor, largura, etc. Para análise de dados poderosa, você pode querer adicionar algumas linhas de tendência de média móvel com intervalos de tempo diferentes para ver como a tendência evolui. A seguinte captura de tela mostra as linhas de tendência de média móvel de 2 meses (verde) e 3 meses (tijolo vermelho): Bem, isso é tudo sobre como calcular a média móvel no Excel. A planilha de exemplo com fórmulas de média móvel e linha de tendência está disponível para download - planilha de Moving Average. Agradeço a leitura e espero vê-lo na próxima semana. A linha azul suaviza as flutuações aleatórias, diz uma tendência menos sobre-reativa Eu percebi recentemente que este tópico nunca Foi coberto antes, em sua forma mais direta, neste site Na verdade, foi o tema de um convidado post pelo estimado David Churchward. E também pelo igualmente estimado Kasper de Jonge. Mas nenhum desses postos beneficiou das funções v2 disponíveis para nós hoje). Para ilustrar o que podemos fazer com as fórmulas Power Pivot mais avançadas, vamos começar com este modelo simples: E um pivô simples: A unidade vendida é a linha vermelha irregular no gráfico no topo da mensagem e Sua fórmula é muito simples: Unidades Vendidas SUM (SalesQtySold) E nós queremos uma versão de Unidades Vendidas que é suavizada ao longo de um período de 3 meses. Moving Sum Permite começar com uma fórmula que é uma soma dos últimos 3 meses (incluindo o atual): 3 meses Moving Sum Unidades vendidas CALCULATE (Unidades vendidas, DATESINPERIOD (CalendarDate, LASTDATE (CalendarDate), - 3, Month)) E ver o que se parece com: Mover Soma de 3 Meses Reflete o Mês Atual e os Dois Meses Anterior Mover Primeira Tentativa Média OK, mas esse número é maior que um único mês e não corresponde à escala do nosso negócio do mundo real, por isso nós Wouldnt querem gráfico que queremos a versão média do que. É uma quantia móvel de 3 meses, de modo a obter a média, poderíamos apenas dividir por 3: 3 Mês Divisão média 3 3 Mês Movendo Soma Unidades Vendidas / 3 Qual parece: 3 Mês Movendo Médio Via Divide por 3 Tem um inconveniente Aqueles Primeiros dois meses, já que são os dois primeiros meses em nosso calendário, estão somando menos de 3 meses de vendas, mas ainda dividindo por 3. Então, injustamente, diminui sua média. Média Móvel Corrigida Podemos explicar isso mudando nosso denominador para usar uma lógica semelhante ao numerador: 3 Mês Mover Média Corrigido 3 Mês Mover Soma Unidades Vendidas / CALCULAR (DISTINCTCOUNT (Calendário Mês), DATESINPERIOD (CalendarDate, LASTDATE (CalendarDate) -3, mês)) Em inglês: tomar a medida de soma de 3 meses que já temos e dividi-la pelo número de meses distintos (únicos) que temos sobre esse mesmo período de 3 meses. Esta Calc é mais justa para os meses nas variações de início Há uma série de variações nesta abordagem diária / semanal / trimestral versões, corrigindo para calendários que se estendem para além do intervalo de datas onde você tem vendas, adaptando-o para calendários personalizados através do A maior fórmula do mundo. Etc, mas vou esperar e ver o que as pessoas perguntam sobre os comentários antes de cavar em qualquer um desses. Um dos fundadores da Power Pivot durante sua carreira de 14 anos na Microsoft e criador do primeiro serviço Power Pivot da nuvem, Rob é uma das principais autoridades em inteligência de negócios de auto-atendimento e tecnologia de planilha de próxima geração . Este post tem 27 comentários É complicado controlar 8220X meses moving8221 do Excel Ter uma célula no Excel onde podemos colocar 3, 6, 12 ou mesmo 1. Ele funciona But8230 .. Eu estou trabalhando com dias 8211 especificamente, eu preciso para obter Uma média contínua de 28 dias de uma das minhas medidas. Isso funciona bem quando eu exibir o resultado com meu atributo de data. No entanto, a minha dimensão do calendário também tem um atributo discreto e não contínuo chamado DateMonth (que se parece com 1-Jan, 2-Jan, etc.) Eu estava esperando que isso me permitiria então pegar meu atributo de ano e comparar a Média de 28 dias para O primeiro de janeiro através de vários anos. Assim, você poderia ter um gráfico de linha onde o eixo é 1-Jan to 31-Dec (sem referência ao ano) ea categoria série é por ano, então uma linha para cada ano. Espero que alguém poderia ajudar A última equação fornecida 8211 Moving Average Corrigido 8211 oferece um denominador com um valor de 12 (ou seja, é dependente de quando meu Calendaryear mês começa. Então, para ter uma correta 12 meses de média móvel, os meus dados e calendário precisa Iniciar ao mesmo tempo). Todas as sugestões que eu descobri: O que fiz foi começar a contar meus meses a partir da primeira data das minhas vendas (ou seja, 40663 é a última data antes de eu querer começar a contar os meses em que minhas vendas foram). Espero que isso possa ajudar alguém a procurar neste post do blog William, você é uma poupança de vida. Eu estava tentando descobrir por que meu denominador detido 3 para o mês mais antigo de dados que eu estava trabalhando. Agradeço seu exemplo e explicação que se aplicam exatamente à minha situação. John pullin diz: Eu tenho uma melhor solução para o comentário final aqui 8211 um que calcula o número de meses para dividir, se os seus dados e calendário não iniciar ao mesmo tempo. Este foi o calc original: mês 3 Mês Mover Mover Mover Mover Unidades vendidas / CALCULATE (DISTINCTCOUNT (CalendarYear mês), DATESINPERIOD (CalendarDate, LASTDATE (CalendarDate), - 3, mês)) e está dentro do divisor que a edição é Necessário 8211 para corretamente trabalhar para fora onde dividir por 2 e, em seguida, 1 (para os primeiros 3 meses em seus dados). O seguinte trabalha um deleite: Média móvel de 3 meses 8211 Trabalhando 3 meses Mover Sum / CALCULATE (CALCULATE (COUNTROWS (VALDOS (DimDateCalendarMonth)), DATESINPERIOD (DimDateDatekey, LASTDATE (DimDateDatekey), - 3, MÊS)) Onde FactSales é Sua tabela de fatos obvs. Espero que isso ajude alguém a sair 8211 como ele me levou louco Vikas Gautam diz: Eu tenho uma pergunta. E se precisarmos da média dos últimos 30 dias de negociação ou dias úteis. Naturalmente, nós teríamos uma coluna da data que representa dias negociando. E quantidade de vendas correspondente. 1 mês de média com a sua fórmula won8217t ser a mesma coisa. Como Exact 30 dias8217s média é necessária aqui. Você poderia por favor sugerir uma fórmula. O difícil é que o cálculo se baseie na data financeira diferente da data do calendário, uma vez que quase todas as funções de data inteligentes existentes não podem ser utilizadas. Qualquer fórmula de cálculo de amostra com base na data financeira eu tenho uma abundância de informações sobre os cálculos com base no cliente datas a partir do link acima 8220Greatest Fórmula no mundo8221. It8217s muito helpful8230 .. Esta é uma grande postagem por todos. Eu quero saber o que se as vendas em um dos últimos três meses é zero ou nenhuma venda em tudo, então como as fórmulas sugeridas trabalho. Eu quero descobrir o número de meses do mês atual para últimos três meses tendo vendas. Digamos que estou em maio de 2017 nos últimos três meses estará incluindo maio de 2017. março de 2017, abril de 2017 e maio de 2017. Mas abril de 2017 não tem vendas. Portanto, deve ser a soma de março e maio de 2017 vendas dividido por 2 e não 3. Suponha que temos vendas para Jun 2017 e Jul 2017. Para junho de 2017 a média dos últimos 3 meses deve ser (My 2017 Jun 2017) / 2. Para Jul 2017 a média dos últimos 3 meses deve ser (May 2017 Jun 2017 Jul 2017) / 3. Qualquer ajuda para alcançar este objectivo será muito apreciada. Ei, solução brilhante. Mas agora estou procurando a mesma adição que CheenuSing mencionou. Algumas séries em meus dados don8217t têm vendas no último período, mas outros fazem. CheenuSing, você encontrou a solução para que I8217d ser muito grato, obrigado. Im novo com todas as funções DAX e bastante lutando com o meu cálculo de média móvel. Eu só preciso a média móvel de 3 meses de volta não incluindo o mês atual. Eu tentei usar 8220IF8221 mas nunca funcionou. Alguém pode me ajudar com ele? O SampP 500 fechou setembro com uma perda mensal de 0,12, coincidentemente sua segunda perda consecutiva de 0,12. Todos os três SampP 500 MAs estão sinalizando investido e todos os cinco Ivy Carteira ETF MAs estão sinalizando investido. Na tabela, fechamentos mensais que estão dentro de 2 de um sinal são realçados em amarelo. A tabela acima mostra o atual sinal de média móvel simples de 10 meses (SMA) para cada um dos cinco ETFs apresentados no The Ivy Portfolio. Weve também incluiu uma tabela de 12 meses SMAs para os mesmos ETFs para esta estratégia alternativa popular. Para uma análise facinating da estratégia da carteira do Ivy, veja este artigo por Adam Butler, Mike Philbrick e Rodrigo Gordillo: Backtesting Movendo Médias Durante os últimos anos weve usou Excel para seguir o desempenho de várias estratégias de sincronização da média móvel. Mas agora usamos as ferramentas de backtesting disponíveis no website do ETFReplay. Quem está interessado em timing de mercado com ETFs deve ter um olhar para este site. Aqui estão as duas ferramentas que usamos com mais freqüência: Antecedentes em médias móveis Comprar e vender com base em uma média móvel de encerramentos mensais pode ser uma estratégia eficaz para gerenciar o risco de perda grave dos principais mercados de baixa. Em essência, quando o fechamento mensal do índice está acima do valor da média móvel, você mantém o índice. Quando o índice é fechado abaixo, você move para o caixa. A desvantagem é que ele nunca recebe você no topo ou de volta na parte inferior. Além disso, ele pode produzir o whipsaw ocasional (curto prazo comprar ou vender sinal), como weve ocasionalmente experimentado ao longo do ano passado. No entanto, um gráfico do SampP 500 encerra mensalmente desde 1995 mostra que uma média de 10 ou 12 meses simples estratégia média (SMA) teria segurado participação na maior parte do movimento de preços ascendentes, reduzindo drasticamente as perdas. Aqui está a variante de 12 meses: A média móvel exponencial de 10 meses (EMA) é uma variante ligeira na média móvel simples. Esta versão aumenta matematicamente a ponderação de dados mais recentes na seqüência de 10 meses. Desde 1995 tem produzido menos whipsaws do que a média móvel simples equivalente, embora fosse um mês mais lento para sinalizar uma venda após estes dois tops do mercado. Um olhar para trás nas médias móveis 10 e 12 meses no Dow durante o Crash de 1929 e Grande Depressão mostra a eficácia destas estratégias durante aqueles tempos perigosos. A Psicologia dos Sinais Momentum Timing funciona por causa de um traço humano básico. As pessoas imitam o comportamento bem-sucedido. Quando ouvem de outro que faz o dinheiro no mercado, compram dentro. Eventualmente a tendência inverte. Pode ser meramente as expansões e contrações normais do ciclo econômico. Às vezes, a causa é mais dramática mdash uma bolha de ativos, uma grande guerra, uma pandemia, ou um choque financeiro inesperado. Quando a tendência inverte, os investidores de sucesso vendem cedo. A imitação do sucesso transforma gradualmente o momentum de compra anterior em vender o momentum. Implementando a estratégia Nossas ilustrações do SampP 500 são apenas ilustrações mdash. Nós usamos o SampP por causa dos dados históricos extensivos thats prontamente disponíveis. No entanto, os seguidores de uma estratégia de média móvel deve tomar decisões de compra / venda sobre os sinais para cada investimento específico, e não um índice amplo. Mesmo se você está investindo em um fundo que rastreia o SampP 500 (por exemplo, Vanguards VFINX ou SPY ETF) os sinais de média móvel para os fundos, ocasionalmente, diferem do índice subjacente por causa do reinvestimento de dividendos. Os números do SampP 500 em nossas ilustrações excluem dividendos. A estratégia é mais eficaz em uma conta de vantagem fiscal com um serviço de corretagem de baixo custo. Você quer os ganhos para si mesmo, não seu corretor ou seu tio Sam. Nota . Para quem quiser ver as médias móveis simples de 10 e 12 meses no SampP 500 e as posições de equidade versus dinheiro desde 1950, está um arquivo Excel (formato xls) dos dados. Nossa fonte para os encerramentos mensais (Coluna B) é Yahoo Finance. As colunas D e F mostram as posições assinaladas pelo fim do mês para as duas estratégias SMA. No passado weve recomendado Mebane Fabers artigo pensativo Uma Abordagem Quantitativa Atribuição Táctica de Ativos. O artigo foi atualizado e expandido como parte três: Active Management seu livro The Ivy Portfolio. Co-autor com Eric Richardson. Este é um deve ler para qualquer um contemplando o uso de um sinal de tempo para decisões de investimento. O livro analisa a aplicação das médias móveis do SampP 500 e de quatro classes de ativos adicionais: o Índice EAFE Morgan Stanley Capital International (MSCI EAFE), o Goldman Sachs Commodity Index (GSCI), o National Association of Real Estate Investment Trust Index (NAREIT) Governo dos Estados Unidos 10 anos títulos do Tesouro. Como um recurso regular deste site, tentamos atualizar os sinais no final de cada mês. Para informações adicionais de Mebane Faber, visite seu site, Mebane Faber Research. Nota de rodapé sobre o cálculo das médias móveis mensais: Se você está fazendo seus próprios cálculos de médias móveis para dividendos de ações ou ETFs, você ocasionalmente obterá resultados diferentes se você não ajustar para dividendos. Por exemplo, em 2017 VNQ permaneceu investido no final de novembro com base em fechamentos mensais ajustados, mas houve um sinal de venda se você ignorou os ajustes de dividendos. Como os dados de meses anteriores serão alterados quando os dividendos forem pagos, você deve atualizar os dados para todos os meses no cálculo se um dividendo tiver sido pago desde o fechamento mensal anterior. Este será o caso de quaisquer ações ou fundos que pagam dividendos. Calcular uma média móvel em Excel Tendo picos em seus dados às vezes pode dificultar a realização de análise preditiva. Esses picos podem ser devido a flutuações sazonais nas vendas, por exemplo mais vendas de refrigerantes e sorvete no verão, ou mesmo vendas de apertos de sapato de gelo no inverno. Exemplos extremos Eu sei, mas as flutuações de volume podem ser extremas. Assim, como eu disse, isso pode tornar a análise complicada às vezes, procurando médias ao longo de um ano inteiro para definir metas mensais nem sempre é o método mais confiável ou mais reflectve da situação da vida real, então eu prefiro usar uma média móvel de três Meses. It8217s realmente simples de fazer no Excel usando a função média. Como você pode ver abaixo tenho meus primeiros meses de dados já inseridos nas células B4 e B6. Na célula C6 digite a fórmula AVERAGE (B4: B6). Excel exibirá um erro como você está ignorando dados semelhantes na coluna de vendas, sabemos que, mas obrigado de qualquer maneira para o Excel aviso. Basta clicar em Ignorar erro neste caso. Tudo o que você precisa fazer é clicar duas vezes no identificador de preenchimento e sua fórmula no resto do seu conjunto de dados. Let8217s aumentar o volume um pouco sobre isso e olhar como a média móvel se parece com um gráfico traçado contra os volumes de vendas reais. Como você pode ver eu planejei real versus a média móvel de 3 meses que alisa estes picos em volumes. Acho que definir metas com esta linha média mais suave é mais fácil do que os volumes de dados spiked. Por que não dar isto um ir e sinta-se livre para deixar um comentário sobre como você encontrou. Como você define suas metas se você tiver grandes flutuações em seus volumes ao longo de um período de tempo
No comments:
Post a Comment