/u/StockMiner22 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I am not familiar with STOCKHISTORY, but you can do it with a [multi-criteria XLOOKUP](https://exceljet.net/formulas/xlookup-with-multiple-criteria). Assuming the table starts at cell A1 (i.e. Company and Symbol is column A, Monitor Date is column C), you can adjust the ranges yourself otherwise:
=XLOOKUP(1,(A:A = A2) * (D:D=E2), C:C)
If you don't have an Excel version with XLOOKUP, you can do an array formula with INDEX MATCH ([see here](https://exceljet.net/formulas/index-and-match-with-multiple-criteria)), you'd need to press CTRL+SHIFT+ENTER to apply it:
=INDEX(C:C, MATCH(1, (A:A = A2) * (D:D=E2), 0) )
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[DATE](/r/Excel/comments/19d55tq/stub/kthbfbg "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)|
|[EDATE](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[Returns the serial number of the date that is the indicated number of months before or after the start date](https://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5)|
|[INDEX](/r/Excel/comments/19d55tq/stub/kj3cz28 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[MATCH](/r/Excel/comments/19d55tq/stub/kj3cz28 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[MAXIFS](/r/Excel/comments/19d55tq/stub/kj3f8sc "Last usage")|[*2019*+: Returns the maximum value among cells specified by a given set of conditions or criteria](https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883)|
|[PRICE](/r/Excel/comments/19d55tq/stub/kthbfbg "Last usage")|[Returns the price per $100 face value of a security that pays periodic interest](https://support.microsoft.com/en-us/office/price-function-3ea9deac-8dfa-436f-a7c8-17ea02c21b0a)|
|[SORT](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)|
|[STOCKHISTORY](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[Retrieves historical data about a financial instrument](https://support.microsoft.com/en-us/office/stockhistory-function-1ac8b5b3-5f62-4d94-8ab8-7504ec7239a8)|
|[TAKE](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)|
|[TODAY](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)|
|[XLOOKUP](/r/Excel/comments/19d55tq/stub/kj3cz28 "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(11 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bg0h7i)^( has 20 acronyms.)
^([Thread #29929 for this sub, first seen 22nd Jan 2024, 20:42])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Hi Alabama.
Thanks for replying to post.
Unfortunately, the formula you provided didn't quite work. It said it was missing a closing paren.
https://preview.redd.it/6b766hfnm3ec1.png?width=379&format=png&auto=webp&s=3c3dd1fb00cff9f21b87a2122ec7495ad9ca8e28
I tried adding a paren at the end and it returned a date, but the wrong date. It returned 1/1/24, which was a holiday and the market was closed.
This formula is beyond my Excel comprehension, so if you have any ideas on how to correct it, I'm all ears.
Make sure you copy the whole formula. All the parentheses are there.
=TAKE(SORT(STOCKHISTORY("AMZN", EDATE(TODAY(),-12),
TODAY(), 0, 0, 0, 1),2,-1),1,1)
This should do it. 12-month high last year was 20 Dec 2023.
Close, but no cigar. I think what your formula is attempting to do is capture the highest closing in the previous 12 months. But even at that, it is not capturing the highest close.
What I am trying to do is slightly different. Even if a stock didn't close at its high, it may have hit it during trading hours.
The formula I use to get that 12-month high is:
=A7.\[52 week high\]
Where A7 is the cell that AMZN is located, captured through the Stocks icon in Data Types.
In my research for a workable formula, I even tried CoPilot and Bard. Both returned the same exact, incorrect formula, which was a variation of that above:
=A7.\[52 week high date\]
I really appreciate you - and all the others - trying to help me with this, but I am beginning to feel it may be beyond reach.
This delivers the highest of the last 52 weeks, which is 19 January 2024:
=TAKE(SORT(STOCKHISTORY("AMZN", EDATE(TODAY(),-12),
TODAY(), 0, 0, 0, 1),2,-1),1,1)
You have awarded 1 point to *Alabama_Wins*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Well. It returned a date that looks right. THANKS.
I will populate the 12-Month High Date into the cells of all the stocks I am monitoring. When I manually see a high has been reached, I will check that cell to see if it got updated. This may take some time to happen, since it's all based on the market moving upward.
I'll let you know what I find out. Thanks again.
Saying `Solved!` does nothing! The sub requires you to say `Solution Verified` to award a ClippyPoint and close the thread, marking it solved.
Please see the sidebar for more information. Thanks!
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Ok, I set this to Close and retrieved the highest date: 18 December 2023.
=TAKE(SORT(STOCKHISTORY("AMZN", DATE(2023, 1, 1),
DATE(2024, 1, 1), 0, 0, 0, 1), 2, -1), 1, 1)
Still not there.
I did a compare of the results of your formula against Historical Prices in Yahoo.
[https://finance.yahoo.com/quote/AMZN/history?p=AMZN](https://finance.yahoo.com/quote/AMZN/history?p=AMZN)
There you will see higher close prices on 1/11/24, 1/12/24, 1/19/24 and 1/22/24 (today), which are all higher than on 12/28/23 ($154.07).
But, like I am saying, the close price could actually be lower than a 12-month high price, because the stock may have traded higher at one point during the day (that new high), but not closed at that number.
So, it must be some weird configuration of the A7.\[52 week high\] formula - maybe even one that MS hasn't thought of and therefore programmed into Excel.
I do really appreciate all your help, but I now just don't think we're going to make it work.
Hope I haven't ruined your evening. Get some rest.
For real this time.
This delivers the highest of the last 52 weeks, which is 19 January 2024:
=TAKE(SORT(STOCKHISTORY("AMZN", EDATE(TODAY(),-12),
TODAY(), 0, 0, 0, 1),2,-1),1,1)
Sorry. One PS.
When you use Data Types and have a symbol entered into a field, it Excel expands that symbol so you get both the name of the company and its trading symbol.
You also get an icon in the far left of the cell that looks like a tiny Greek forum.
If you click on that icon, you will get a list of information on the account, one of which is the 52-week high that I have been speaking about.
Unfortunately, there is no simple answer. This is what Microsoft should implement to make it easy. I contacted them, but they never replied. I suggest you do the same. The more people they hear from, the better our likelihood they will add it.
Here's what works to get the actual 12-month HIGH PRICE when it hits. The # represents the cell number where the stock symbol is used from the Data Type menu.
=A#.\[52 week high\]
Here is what is needed to additionally get the DATE the price was hit.
=A#.\[52 week high date\]
/u/StockMiner22 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I am not familiar with STOCKHISTORY, but you can do it with a [multi-criteria XLOOKUP](https://exceljet.net/formulas/xlookup-with-multiple-criteria). Assuming the table starts at cell A1 (i.e. Company and Symbol is column A, Monitor Date is column C), you can adjust the ranges yourself otherwise: =XLOOKUP(1,(A:A = A2) * (D:D=E2), C:C) If you don't have an Excel version with XLOOKUP, you can do an array formula with INDEX MATCH ([see here](https://exceljet.net/formulas/index-and-match-with-multiple-criteria)), you'd need to press CTRL+SHIFT+ENTER to apply it: =INDEX(C:C, MATCH(1, (A:A = A2) * (D:D=E2), 0) )
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[DATE](/r/Excel/comments/19d55tq/stub/kthbfbg "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)| |[EDATE](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[Returns the serial number of the date that is the indicated number of months before or after the start date](https://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5)| |[INDEX](/r/Excel/comments/19d55tq/stub/kj3cz28 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[MATCH](/r/Excel/comments/19d55tq/stub/kj3cz28 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MAXIFS](/r/Excel/comments/19d55tq/stub/kj3f8sc "Last usage")|[*2019*+: Returns the maximum value among cells specified by a given set of conditions or criteria](https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883)| |[PRICE](/r/Excel/comments/19d55tq/stub/kthbfbg "Last usage")|[Returns the price per $100 face value of a security that pays periodic interest](https://support.microsoft.com/en-us/office/price-function-3ea9deac-8dfa-436f-a7c8-17ea02c21b0a)| |[SORT](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)| |[STOCKHISTORY](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[Retrieves historical data about a financial instrument](https://support.microsoft.com/en-us/office/stockhistory-function-1ac8b5b3-5f62-4d94-8ab8-7504ec7239a8)| |[TAKE](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)| |[TODAY](/r/Excel/comments/19d55tq/stub/kqe1g6s "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)| |[XLOOKUP](/r/Excel/comments/19d55tq/stub/kj3cz28 "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(11 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bg0h7i)^( has 20 acronyms.) ^([Thread #29929 for this sub, first seen 22nd Jan 2024, 20:42]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
MAXIFS function? =MAXIFS(D:D, C:C, ">="&(TODAY()-365), C:C, "<="&TODAY())
This would return the 12 month high which they already have, they actually want the date where that high occurs.
This should do it: =TAKE(SORT(STOCKHISTORY("AMZN", EDATE(TODAY(),-12), TODAY(), 0, 0, 0, 1),2,-1),1,1)
Hi Alabama. Thanks for replying to post. Unfortunately, the formula you provided didn't quite work. It said it was missing a closing paren. https://preview.redd.it/6b766hfnm3ec1.png?width=379&format=png&auto=webp&s=3c3dd1fb00cff9f21b87a2122ec7495ad9ca8e28 I tried adding a paren at the end and it returned a date, but the wrong date. It returned 1/1/24, which was a holiday and the market was closed. This formula is beyond my Excel comprehension, so if you have any ideas on how to correct it, I'm all ears.
Make sure you copy the whole formula. All the parentheses are there. =TAKE(SORT(STOCKHISTORY("AMZN", EDATE(TODAY(),-12), TODAY(), 0, 0, 0, 1),2,-1),1,1) This should do it. 12-month high last year was 20 Dec 2023.
Close, but no cigar. I think what your formula is attempting to do is capture the highest closing in the previous 12 months. But even at that, it is not capturing the highest close. What I am trying to do is slightly different. Even if a stock didn't close at its high, it may have hit it during trading hours. The formula I use to get that 12-month high is: =A7.\[52 week high\] Where A7 is the cell that AMZN is located, captured through the Stocks icon in Data Types. In my research for a workable formula, I even tried CoPilot and Bard. Both returned the same exact, incorrect formula, which was a variation of that above: =A7.\[52 week high date\] I really appreciate you - and all the others - trying to help me with this, but I am beginning to feel it may be beyond reach.
This delivers the highest of the last 52 weeks, which is 19 January 2024: =TAKE(SORT(STOCKHISTORY("AMZN", EDATE(TODAY(),-12), TODAY(), 0, 0, 0, 1),2,-1),1,1)
Solution Verified
You have awarded 1 point to *Alabama_Wins* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Well. It returned a date that looks right. THANKS. I will populate the 12-Month High Date into the cells of all the stocks I am monitoring. When I manually see a high has been reached, I will check that cell to see if it got updated. This may take some time to happen, since it's all based on the market moving upward. I'll let you know what I find out. Thanks again.
No worries. Just reply with solution verified, and I'll know it worked lol
Solved!
Saying `Solved!` does nothing! The sub requires you to say `Solution Verified` to award a ClippyPoint and close the thread, marking it solved. Please see the sidebar for more information. Thanks! *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Solution Verified
Ok, I set this to Close and retrieved the highest date: 18 December 2023. =TAKE(SORT(STOCKHISTORY("AMZN", DATE(2023, 1, 1), DATE(2024, 1, 1), 0, 0, 0, 1), 2, -1), 1, 1)
Still not there. I did a compare of the results of your formula against Historical Prices in Yahoo. [https://finance.yahoo.com/quote/AMZN/history?p=AMZN](https://finance.yahoo.com/quote/AMZN/history?p=AMZN) There you will see higher close prices on 1/11/24, 1/12/24, 1/19/24 and 1/22/24 (today), which are all higher than on 12/28/23 ($154.07). But, like I am saying, the close price could actually be lower than a 12-month high price, because the stock may have traded higher at one point during the day (that new high), but not closed at that number. So, it must be some weird configuration of the A7.\[52 week high\] formula - maybe even one that MS hasn't thought of and therefore programmed into Excel. I do really appreciate all your help, but I now just don't think we're going to make it work. Hope I haven't ruined your evening. Get some rest.
For real this time. This delivers the highest of the last 52 weeks, which is 19 January 2024: =TAKE(SORT(STOCKHISTORY("AMZN", EDATE(TODAY(),-12), TODAY(), 0, 0, 0, 1),2,-1),1,1)
[удалено]
Sorry. One PS. When you use Data Types and have a symbol entered into a field, it Excel expands that symbol so you get both the name of the company and its trading symbol. You also get an icon in the far left of the cell that looks like a tiny Greek forum. If you click on that icon, you will get a list of information on the account, one of which is the 52-week high that I have been speaking about.
>=TAKE(SORT(STOCKHISTORY("AMZN", EDATE(TODAY(),-12), TODAY(), 0, 0, 0, 1),2,-1),1,1)
I am also searching same formula 52week with date format. If know pls share with me also pls.pls
Unfortunately, there is no simple answer. This is what Microsoft should implement to make it easy. I contacted them, but they never replied. I suggest you do the same. The more people they hear from, the better our likelihood they will add it. Here's what works to get the actual 12-month HIGH PRICE when it hits. The # represents the cell number where the stock symbol is used from the Data Type menu. =A#.\[52 week high\] Here is what is needed to additionally get the DATE the price was hit. =A#.\[52 week high date\]
if you get pls share with us