Share via

Stockhistory Additional Column only with Country Stock Ticker?

Anonymous
2025-01-11T02:34:20+00:00

Hi. I use Excel Stockhistory to look back 18 months for Historical Stocks Price by month end. I Transpose the historical prices across 18 columns so I can have a list of stock history down the page. One quirky thing happens if I have to include the County before the Stock Ticker. When I add the Country Code in the Stock Ticker the result produces 19 Columns. See screenshot example below where it has added an additional Column?

This is not a show stopper for me. but does anyone know why it is doing this? I have used different Stock Tickers and it does the same thing every time when I add the County Code?

Thanks...

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. riny 20,870 Reputation points Volunteer Moderator
    2025-01-11T10:09:22+00:00

    I can't replicate that behaviour.

    Can you show the formulas you use for both? It seems the end date for the second ticker (with "XASX") shifted to the end of the current month, resulting in the last available rate for January at the time of refreshing (in your case January 10).

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. riny 20,870 Reputation points Volunteer Moderator
    2025-01-12T06:58:51+00:00

    Thought the matter through a bit more, but don't understand why adding the "XASX:" would change the result. I can't replicate that behaviour.

    Anyway, if you really want the last 18 month-end close values and not add a 19th for the current close, use the EOMONTH function to make it work. If your Excel still insists on adding a 19th value just by adding the market code like XASX that's odd. Something else must be wrong in your set-up.

    Start: B2

    =EOMONTH(B4,-17)

    End: B4

    =EOMONTH(TODAY(),-1)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-01-12T03:31:10+00:00

    Hi Riny,

    I worked out why Excel added the additional formula cell/column for me and not for you.

    In your reply, I was curious why your Excel version didn't do it for you. I wondered if it had something to do with me using the Today() formula, as you typed your Start and End Dates into the sheet, whereas I used the dynamic Today() date formulas as below.

    Find my formulas below. I copy the formula down to create a new row.

    Formula: =TRANSPOSE(STOCKHISTORY(A28,$B$2,$B$4,2,0,1))

    Start Date: =EDATE(TODAY(),-18)

    End Date: =TODAY()

    (My start and end dates are at B2 and B4)

    When using the Dynamic Date formulas and I add the Country Code to the Stock Ticker it did add the formula to the cell in the last column.

    The question was "why".

    If I remove the Country Code at A28 (as in my example) and hit enter, the formula at the last column, U28, disappears, as in the row above.

    It didn't matter what Stock Ticker I used, as soon as I had to include the Country Code it added the formula to the last cell in the additional column resulting in 19 columns, not 18.

    But; If I manually input the Start and End Dates it works fine, the same as your example it doesn't add the additional cell/column.

    Therefore I was left with the question; could I use the Today() formula somehow without adding the formula to the last column/cell in my spreadsheet?

    It turns out that by using the formula =Today() as the end date, the Stockhistory function adds in the Last Closing date value, as well as the 18-month historical data. I.E: Column 19 shows the current closing stock value as of today's date.

    Bonus: I don't mind it doing this in my case, I am happy to see the current closing price as well as the 18-month historical data.

    Now that I know what is going on I feel a bit stupid thinking it was occurring because of adding the country code to the stock ticker. (It was Not the issue!)

    I realized what was going on when I deleted the formula =Today() to input the date manually to test the sheet and then when I input the =Today() formula again, it updated the sheet and added the Row Formula to the last Cell/Column for all of my Stocks, I realized it was actually the Today Formula that was causing the issue.

    Note: I don't know why the Formula reverted back to 18 months when I was deleting the Stock Ticker Country Code, but I don't think I want to know.

    Thanks for your time...

    Was this answer helpful?

    0 comments