VBA with Stockhistory

[CALVIN2021]

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

[#2]

Hello

[#3]

Admins, Hope I followed the community rules here.

@CALVIN2021 Hi, I too came here for the answer and went through the other forums but couldn't find the solution. The problem seems that StockHistory function shows #Busy while the VBA code is in process however the data appears once the macro is completed. As I too wanted to have more than 10 stocks historical data from a single macro and found a workaround which worked for me but its not a solution. Maybe someone from the esteemed programmer community can recommend solution. Hope this makes little easier for you too. I split my macro's into two parts - 

A. Pull all the data first into multiple tabs as per the stock name and the data will appear as the macro function is completed.

B. Once the first macro completes, you can use your second macro to start the remaining work.

First Macro lines for workaround

Sub PullStockData()

For i = 1 To 6
Sheets("Stocks").Activate ' ---- All the stocks name are listed in this sheet
Stock = Range("A" & i).Value
StartDate = Range("E1").Value
EndDate = Range("E2").Value
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "S_" & Stock
Range("A1").Select
ActiveCell.Formula2R1C1 = _
"=STOCKHISTORY(""XNSE:"" & """ & Stock & """,""" & StartDate & """,""" & EndDate & """,0,1,0,1,2,3,4,5)"
Next i

End Sub