wilsonx63

%3CLINGO-SUB%20id%3D%22lingo-sub-1525302%22%20slang%3D%22en-US%22%3Ewilsonx63%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525302%22%20slang%3D%22en-US%22%3Estock%20function%3A%20I%20need%20to%20redeem%20stock%20closing%20quotes%20from%20previous%20months.%20how%20do%20I%20do.%3CBR%20%2F%3E%3CBR%20%2F%3Emy%20excel%20is%20365%20insider%20beta%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1525302%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525337%22%20slang%3D%22en-US%22%3ERe%3A%20wilsonx63%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F708112%22%20target%3D%22_blank%22%3E%40wilsonx63%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Stocks%20Data%20Type%20won't%20get%20historical%20prices.%20But%20you%20can%20use%20the%20technique%20described%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excel-university.com%2Farticles%2Fcal-cpa%2Fget-current-and-historical-stock-prices-into-excel%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EExcel%20University%3A%20Get%20Current%20and%20Historical%20Stock%20Prices%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20drawback%20is%20you%20need%20to%20pass%20one%20stock%20code%20at%20a%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20that%20technique%20and%20a%20date%20filter%20to%20get%20AMZN%20prices%20from%20June%20of%20this%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20query%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Web.Page(Web.Contents(%22https%3A%2F%2Ffinance.yahoo.com%2Fquote%2FAMZN%2Fhistory%2F%22))%2C%0A%20%20%20%20Data2%20%3D%20Source%7B2%7D%5BData%5D%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(Data2%2C%7B%7B%22Date%22%2C%20type%20date%7D%2C%20%7B%22Open%22%2C%20type%20number%7D%2C%20%7B%22High%22%2C%20type%20number%7D%2C%20%7B%22Low%22%2C%20type%20number%7D%2C%20%7B%22Close*%22%2C%20type%20number%7D%2C%20%7B%22Adj%20Close**%22%2C%20type%20number%7D%2C%20%7B%22Volume%22%2C%20Int64.Type%7D%7D)%2C%0A%20%20%20%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(%23%22Changed%20Type%22%2C%20each%20Date.IsInPreviousNMonths(%5BDate%5D%2C%201))%0Ain%0A%20%20%20%20%23%22Filtered%20Rows%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1530038%22%20slang%3D%22en-US%22%3ERe%3A%20wilsonx63%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1530038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F708112%22%20target%3D%22_blank%22%3E%40wilsonx63%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20Insiders%20Beta%20STOCKHISTORY()%20shall%20be%20available%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20967px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206126i71BF25FCC3617BD3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSORT(STOCKHISTORY(%22AMZN%22%2CDATE(2020%2C6%2C1)%2CDATE(2020%2C6%2C30)%2C0%2C2%2C0%2C2%2C3%2C4%2C1%2C5)%2C%2C-1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
stock function: I need to redeem stock closing quotes from previous months. how do I do.

my excel is 365 insider beta
2 Replies

@wilsonx63 

 

The Stocks Data Type won't get historical prices. But you can use the technique described here:

Excel University: Get Current and Historical Stock Prices 

 

The drawback is you need to pass one stock code at a time.

 

I used that technique and a date filter to get AMZN prices from June of this year.

 

This is the query:

 

let
    Source = Web.Page(Web.Contents("https://finance.yahoo.com/quote/AMZN/history/")),
    Data2 = Source{2}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data2,{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close*", type number}, {"Adj Close**", type number}, {"Volume", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNMonths([Date], 1))
in
    #"Filtered Rows"

 

 

@wilsonx63 

For Insiders Beta STOCKHISTORY() shall be available

image.png

formula is

=SORT(STOCKHISTORY("AMZN",DATE(2020,6,1),DATE(2020,6,30),0,2,0,2,3,4,1,5),,-1)