Moving Average Array Vba

Sempre usei referências de alcance em planilhas para cálculos, mas agora estou começando a usar arrays para trabalhar com dados, devido ao quanto eles podem ser mais rápidos. Eu vou lidar com bilhões de cálculos, então eu preciso fazer o máximo possível na memória. No trecho de código fornecido, estou tentando obter SMAMINArray para calcular uma média móvel de 50 períodos dos 50 valores anteriores da matriz chamada vArray. Alguém sabe como consegui-lo. Aqui está o que eu tenho até agora, e não consigo fazer isso funcionar. A Soma (vArray (j, j49) 50) é o culpado. Eu sei que não está certo, mas eu precisava colocar uma ideia para alguém com uma experiência muito maior do que eu para interpretar o que eu realmente quero. Eu simplesmente não sei como traduzir isso em somar os itens 1 a 50 e dividir o total em 50. E o segundo elemento do SMAMINArray calcularia então a média dos elementos 2 a 51, etc. Qualquer idéia Aqui está um trecho de algum código Que eu tentei: (Se você mora em Dallas, vou comprar uma bebida de sua escolha para obter ajuda sobre este problema) perguntou 18 de março 15 às 3:41 A macro abaixo mostra como alcançar o resultado que eu acredito que você procura. Eu configurei a célula A1 da planilha MoveAvg para Rand () 1000. Arrastei isso para a célula A1500. Eu fiz um Paste Special of Values ​​apenas para esse intervalo para que os valores aleatórios fossem corrigidos. Eu ajuste Cell C50 para a média de Cells A1: 50. Arrastei isso para baixo. Eu corri a macro abaixo que cria uma matriz contendo as médias móveis. Esta matriz é escrita na coluna E a partir da linha 50. A imagem abaixo mostra que os valores calculados pelo Excel correspondem aos calculados pela macro. No entanto, o VBA não é o idioma a ser usado, você está executando bilhões de cálculos. Respondi a uma pergunta recente que envolveu o cálculo de 8.063 milhões de valores e a sua escrita no disco com 1 milhão de valores por arquivo. Cálculo e economia de 50 milhões de valores com VBA levou 13 minutos, 15 segundos. A multiplicação sugere um tempo total para todos os 8,063 milhões de valores de quase 14 dias, 20 horas. Com o VB. Net, calcular e armazenar todos os 8,063 milhões de valores demorou 51 minutos. Ler e escrever em um livro do Excel com o VB. Net é mais lento que com o VBA. Mas uma vez que os dados estão dentro dos arrays VB. Net, os cálculos são milhares de vezes mais rápidos. O VB. Net é uma atualização do VBA para que a curva de aprendizado não seja íngreme. A versão Express do VB. Net é gratuita e você pode distribuir qualquer programa que você escreva se você se registrar. Eu deixo você pensar sobre isso. Eu quero calcular uma média móvel do último, digamos 20, números de uma coluna. Um problema é que algumas das células da coluna podem estar vazias, elas devem ser ignoradas. Exemplo: uma média móvel dos últimos três seria (155167201) 3. Eu tentei implementar isso usando média, offset, índice, mas eu simplesmente não sei como. Estou um pouco familiarizado com as macros, então essa solução funcionaria bem: MovingAverage (A13) Obrigado por quaisquer dicas ou soluções solicitadas 12 de março 11 às 15:36 Digite isso com controlshiftenter para torná-lo uma fórmula de matriz. Isto irá encontrar os últimos três valores. Se quiser mais ou menos, mude as duas instâncias de 3 na fórmula para o que quiser. Esta parte retorna o 4º número de linha mais alta de todas as células que possuem um valor ou 5 no seu exemplo porque as linhas 6, 8 e 9 são as linhas 1 a 3 maiores com um valor. Esta parte retorna 9 TRUEs ou FALSEs com base em se o número da linha é maior do que o 4º maior. Isso multiplica os valores em A1: A9 por esses 9 TRUEs ou FALSEs. TRUEs são convertidos para 1 e FALSEs para zero. Isso deixa uma função SUM como esta, porque todos os valores acima de 155 não satisfazem o critério do número da linha, o aumento multiplicado por zero. Escrevi um pequeno script no VBA. Espero que faça o que quiser. Aqui você está: 1) Eu estabeleci o limite para 360 células. Isso significa que o script não procurará mais do que 360 ​​células. Se você deseja alterá-lo, mude o valor inicial do contador. 2) O script retorna a média não arredondada. Altere a última linha para MovingAverage Round (CDbl (tmp i), 2) 3) O uso é exatamente como você queria, então digite MovingAverage (a13) na célula. Todos os comentários são bem-vindos. Aqui está um código que deve ser útil para aqueles que usam análise técnica na negociação e que desejam testar estratégias no Excel. Ele calcula a média móvel simples, linearmente ponderada e exponencial. Além disso, vou apresentar e explicar as etapas para criar o formulário e o código VBA. Inserir um UserForm 8211 Nome: MAForm Adicionar quatro etiquetas dos controles Toolbox 8211 Legendas conforme a tela de impressão acima Adicionar uma ComboBox para a seleção de tipo de média móvel. Foi chamado comboTypeMA Adicione dois controles RefEdit para o intervalo de entrada e o intervalo de saída. Adicionar uma caixa de texto para selecionar o período médio móvel Adicionar dois botões: Nome: botãoSubmit, Legenda: Enviar e Nome: botãoCancelar, Legenda: Cancelar Para gerar a lista suspensa para a seleção do tipo MA e carregar o formulário do usuário, um novo módulo Será inserido com o código abaixo. Os itens da ComboBox devem ser preenchidos por tipos de médias móveis e o formulário do usuário será carregado. Option Explicit Sub loadMAForm () Com MAFormboTypeMA. RowSource. AddItem Simples. AddItem Ponderado. AddItem Exponential End Com MAForm. Show End Sub Abaixo está o código atribuído ao botão Enviar. Private Sub buttonSubmitClick () Dim inputRange, outputRange As Range O inputRange conterá a série de preços usada para computar as MAs e o outputRange será preenchido com os valores das médias móveis. Dim InputPeriod As Integer O período médio móvel é declarado. Dim inputAddress, outputAddress As String Os intervalos de entrada e saída declarados como string. Se comboTypeMA. Value ltgt Exponential e comboTypeMA. Value ltgt Simples e comboTypeMA. Value ltgt Weighted True Then MsgBox Selecione um tipo de média móvel da lista. RefInputRange. SetFocus Exit Sub Esta parte do procedimento impõe as primeiras restrições relativas aos dados enviados. Se o tipo de média móvel não estiver contido na lista suspensa, o procedimento não procederá ao próximo passo e o usuário será solicitado a selecioná-lo novamente. ElseIf RefInputRange. Value Então MsgBox Selecione o intervalo de entrada. RefInputRange. SetFocus Exit Sub ElseIf RefOutputRange. Value Então MsgBox Selecione o intervalo de saída. RefOutputRange. SetFocus Exit Sub ElseIf RefInputPeriod. Value Then MsgBox Selecione o período médio móvel. RefInputPeriod. SetFocus Exit Sub ElseIf Not IsNumeric (RefInputPeriod. Value) Então MsgBox Moving período médio deve ser um número. RefInputPeriod. SetFocus Exit Sub End Se outras restrições forem criadas. O intervalo de entrada, o intervalo de saída e o período de entrada não devem estar em branco. Além disso, o período médio móvel deve ser um número. InputAddress RefInputRange. Value Set inputRange Range (inputAddress) outputAddress RefOutputRange. Value Definir outputRange Range (outputAddress) inputPeriod RefInputPeriod. Value Os argumentos para inputRange e outputRange intervalos serão inputAddress e outputAddress declarados como strings. Se inputRange. Columns. Count ltgt 1 Então o intervalo de entrada MsgBox pode ter apenas uma coluna. RefInputRange. SetFocus Exit Sub O inputRange deve conter apenas uma coluna. ElseIf inputRange. Rows. Count ltgt outputRange. Rows. Count Então MsgBox O intervalo de saída tem um número diferente de linhas do que o intervalo de entrada. RefInputRange. SetFocus Exit Sub End If O inputRange e outputRange devem ter um número igual de linhas. Dim RowCount As Integer RowCount inputRange. Rows. Count Dim cRow As Integer ReDim inputarray (1 para RowCount) Para cRow 1 Para RowCount inputarray (cRow) inputRange. Cells (cRow, 1).Value Next cRow inputarray é declarado como matriz e it8217s Corresponde aos valores de cada linha do intervalo de entrada. Se inputPeriod gt RowCount Then MsgBox O número de observações selecionadas é amplificador amp RowCount e o período é amplificador amplificador InputPeriod. O intervalo de entrada deve ter uma quantidade maior ou igual de elementos que o período selecionado. RefInputRange. SetFocus Exit Sub End Se Outra restrição for adicionada 8211 O intervalo de entrada deve ter uma quantidade maior ou igual de elementos do que o período. Se inputPeriod lt 0, então MsgBox Moving período médio deve ser superior a 0. RefInputPeriod. SetFocus Exit Sub End If O período médio móvel deve ser superior a zero. ReDim outputarray (inputPeriod To RowCount) Como Variant Também são determinadas as dimensões da matriz do outputarray. O limite inferior da matriz é o valor inputPeriod eo limite superior é o valor de RowCount (o número de elementos na entradaRange). Abaixo da parte do procedimento calculado a média móvel simples, se a seleção para comboTypeMA for simples. SMA ----------------------------------------- Se comboTypeMA. Value Simples Então Dim i J Como Inteiro Dim temp Como Duplo Para i inputPeriod Para Temporidade RowCount 0 Para j (i - (inputPeriod - 1)) Para i temp temp inputarray (j) Próximo j outputarray (i) entrada temporáriaPeriod outputRange. Cells (i, 1).Value outputarray (i) Next i outputRange. Cells (0, 1).Value SMA (amp inputPeriod amp) Basicamente, o procedimento calcula a média móvel dos últimos números x (x é igual ao inputPeriod), começando pelo elemento de O inputarray igual ao InputPeriod. Abaixo está um exemplo simplificado, que mostra cada etapa do procedimento. Neste exemplo, existem quatro números (no01, no02, no03 e no04) da linha 1 à linha 4 e o período médio móvel é 3. Após cada nova média móvel, cada célula do outputRange irá tirar o valor do Outputarray. E depois de todas as médias móveis são computadas, na célula acima do outputRange será inserido um título contendo o tipo e o período da média móvel. Esta próxima parte calculará a média móvel exponencial. EMA ------------------------------------------ ElseIf comboTypeMA. Value Exponential Then Dim Alfa Como duplo alfa 2 (entradaPeriodo 1) Para j 1 Para entrarPeriod temp temp temparrayar (j) Próximo j outputarray (inputPeriod) temp inputPeriod Primeiro o valor de alpha é determinado. Porque na computação, o valor da EMA é baseado no EMA anterior, o primeiro será a média móvel simples. Para i inputPeriod 1 Para RowCount outputarray (i) outputarray (i - 1) alpha (inputarray (i) - outputarray (i - 1)) Próximo i Começando com a segunda média móvel, eles serão computados com base na fórmula acima: Anterior EMA plus alpha multiplicado pela diferença entre o número atual do inputarray e o valor EMA anterior. Para i inputPeriod Para RowCount outputRange. Cells (i, 1). Outputarray de Válido (i) Próximo i outputRange. Cells (0, 1).Value EMA (amp inputPeriod amp) Assim como o código para SMA, o outputarray será preenchido e A célula acima do outputarray representará o tipo eo período da média móvel. Abaixo está o código para calcular a média móvel ponderada. WMA ------------------------------------------ ElseIf comboTypeMA. Value Weighted Then Dim Temp2 As Integer Para i inputPeriod Para RowCount temp 0 temp2 0 Para j (i - (inputPeriod - 1)) Para i temp temp inputarray (j) (j - i inputPeriod) temp2 temp2 (j - i inputPeriod) Próximo j outputarray (i ) Temp temp2 outputRange. Cells (i, 1). Outputarray de Válido (i) Próximo i outputRange. Cells (0, 1).Value WMA (amp inputPeriod amp) End If A tabela abaixo contém as etapas para calcular cada variável usada para o Cálculo de WMA. Assim como no exemplo anterior, neste há os números na entradaRange. E o período de entrada é 3. Abaixo está o código final do procedimento, que descarrega o formulário de usuário. Descarregar o MAForm End Sub O procedimento abaixo é para o botão Cancelar. Será adicionado no mesmo módulo. Private Sub buttonCancelClick () Descarregar MAForm End Sub