VBA with Stockhistory

%3CLINGO-SUB%20id%3D%22lingo-sub-2241143%22%20slang%3D%22en-US%22%3EVBA%20with%20Stockhistory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2241143%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20excel%20formula%20to%20get%20the%20moving%20average%20of%20a%20particular%20stock%20as%20of%20a%20given%20day%20using%20stockhistory()%20and%20now%20I%20want%20to%20convert%20it%20to%20VBA%20custom%20function%20for%20simplicity%20and%20ease%20of%20debugging.%20here's%20what%20I%20have%20done%20so%20far.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFunction%20MA(Stock%20As%20Variant%2C%20AverDate%20As%20Integer%2C%20AsOf%20As%20Date)%20As%20Long%0A'Ref%3A%20AverDate%20means%20if%20I%20want%20to%20get%20MA120%2C%20120%20will%20be%20AverDate%0A%0A%0A'Step%201%20-%20Getting%20the%20price%20list%20with%20stockhistory()%20(By%20day%2C%20no%20title%2C%20with%20close%20price)%0AList%20%3D%20StockHistory(Stock%2C%20AsOf%20-%20400%2C%20AsOf%2C%200%2C%200%2C%201)%20%20'Get%20the%20dataset%20from%20as%20far%20back%20as%20400%20days%20ago%20for%20later%20maniplulation.%0A%0A'Step%202%20-%20Get%20the%20bottom%20x%20(AverDate)%20close%20price%20of%20the%20list%20and%20sum%20them.%0A'Sum_LastXdays%20%3D%20(PENDING)%20-%20No%20idea%20how%20to%20impliment%0A%0A'Step%203%20-%20round-up%0AMA%20%3D%20Sum_LastXdays%20%2F%20AverDate%0A%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20here's%20my%20questions.%3C%2FP%3E%3CP%3E1.%20I%20can't%20store%20results%20from%20stockhistory()%20to%20a%20variable%20for%20later%20manipulation%20and%20it%20can't%20show%20with%20Debug.print%20or%20Msgbox.%20How%20should%20I%20fix%20that%3F%20(I%20can%20only%20assign%20it%20to%20be%20the%20value%20of%20range(%22A1%22)%20statically%20thought%2C%20but%20it's%20not%20what%20I%20do%20to%20be%20able%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20If%20the%20result%20can%20be%20stored%20into%20a%20variable%2C%20could%20you%20suggest%20me%20a%20couple%20of%20function%20to%20manipulate%20a%20dynamic%20array%3F%20(i.e%3A%201)%20to%20get%20the%20bottom%20five%20number%20of%20the%20list%202)%20In%20case%20I%20have%20two%20column(a%20for%20date%2C%20b%20for%20close%20price)%2C%20to%20get%20the%205th%20latest%20day%20from%20column%20a%20%2C%20and%20get%20its%20corresponding%20number%20from%20column%20b%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2241143%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2250954%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20with%20Stockhistory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2250954%22%20slang%3D%22en-US%22%3EHello%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a excel formula to get the moving average of a particular stock as of a given day using stockhistory() and now I want to convert it to VBA custom function for simplicity and ease of debugging. here's what I have done so far.

 

Function MA(Stock As Variant, AverDate As Integer, AsOf As Date) As Long
'Ref: AverDate means if I want to get MA120, 120 will be AverDate


'Step 1 - Getting the price list with stockhistory() (By day, no title, with close price)
List = StockHistory(Stock, AsOf - 400, AsOf, 0, 0, 1)  'Get the dataset from as far back as 400 days ago for later maniplulation.

'Step 2 - Get the bottom x (AverDate) close price of the list and sum them.
'Sum_LastXdays = (PENDING) - No idea how to impliment

'Step 3 - round-up
MA = Sum_LastXdays / AverDate

End Function

 

and here's my questions.

1. I can't store results from stockhistory() to a variable for later manipulation and it can't show with Debug.print or Msgbox. How should I fix that? (I can only assign it to be the value of range("A1") statically thought, but it's not what I do to be able to do.

 

2. If the result can be stored into a variable, could you suggest me a couple of function to manipulate a dynamic array? (i.e: 1) to get the bottom five number of the list 2) In case I have two column(a for date, b for close price), to get the 5th latest day from column a , and get its corresponding number from column b

 

 

1 Reply
Hello