Closed Thread
Results 1 to 17 of 17

Share Price lookup from Google Finance doesn't always work

  1. #1
    Registered User
    Join Date
    11-28-2022
    Location
    Kingston, ontario
    MS-Off Ver
    using open office and google sheets
    Posts
    13

    Share Price lookup from Google Finance doesn't always work

    I have a Google Sheets spreadsheet that looks up share prices on Google Finance. It mostly works, but I have a strange problem.
    Bank of Commerce has a series of preferred shares. In G-F they like ticker to be in form of TSE:CM-P for example. Examples in this series are CM-O, CM-P, CM-Q etc. They all work, except for CM-O. However, if CM-O or TSE:CM-O is entered directly into Google Finance, then it works.
    Any explanation for this?

    Site won't let me link to sample. It also wants me to check that I am not a robot every 30sec or so??

  2. #2
    Registered User
    Join Date
    11-28-2022
    Location
    Kingston, ontario
    MS-Off Ver
    using open office and google sheets
    Posts
    13

    Re: Share Price lookup from Google Finance doesn't always work

    Tried attaching sample again after3 posts, still no dice

  3. #3
    Registered User
    Join Date
    11-28-2022
    Location
    Kingston, ontario
    MS-Off Ver
    using open office and google sheets
    Posts
    13

    Re: Share Price lookup from Google Finance doesn't always work

    Tried again after 4 posts - still no dice. Maybe try Reddit.

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Share Price lookup from Google Finance doesn't always work

    due to your post count being less than 10 you wont be able to post links straight away.

    try posting link to sample file, with the dots made up of words, and put some spaces into the link, like this:
    google dot com / file/ link
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  5. #5
    Registered User
    Join Date
    11-28-2022
    Location
    Kingston, ontario
    MS-Off Ver
    using open office and google sheets
    Posts
    13

    Re: Share Price lookup from Google Finance doesn't always work

    Thanks for the tip. However, I think I explained the issue without need for the link, and received no responses. I guess nobody here would likely have the answer anyway, so I will just leave it at that.

  6. #6
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Share Price lookup from Google Finance doesn't always work

    P, Q, S, T and Y works for me but not O which is "Non-cumulative Rate Reset Class A Preferred Shares Series 39"

    I believe it's a problem related with Google server.


    Edit: You may use the following formula as well, which fetches the data from "google.com/finance"

    Please Login or Register  to view this content.
    Last edited by Haluk; 12-03-2022 at 01:29 PM.

  7. #7
    Registered User
    Join Date
    11-28-2022
    Location
    Kingston, ontario
    MS-Off Ver
    using open office and google sheets
    Posts
    13

    Re: Share Price lookup from Google Finance doesn't always work

    What is interesting, is that your link to google.com/finance requires the ticker in format CM-O:TSE while the GOOGLEFINANCE function requires it in format TSE:CM-O.

    Still a mystery as to what goes wrong at Google for this ticker.

  8. #8
    Registered User
    Join Date
    11-28-2022
    Location
    Kingston, ontario
    MS-Off Ver
    using open office and google sheets
    Posts
    13

    Re: Share Price lookup from Google Finance doesn't always work

    Additional problem using GOOGLEFINANCE(TICKER , "PRICE") when I have two similar but different tickers. BAM.PR.C and BAM.PF.C. TSE:BAM-C accesses the first one, but how to get to the second one?

    Still can't post links, which makes site useless for new users. (remove the +'s and add h t t p s : // at front.)

    docs.google.com+/spreadsheets+/d/1FeviweO2qH7lhjfGlJH_QLQAvqIuazU_4vperGOX_es/edit?usp=sharing
    Last edited by Freeagent; 12-07-2022 at 10:11 PM.

  9. #9
    Registered User
    Join Date
    11-28-2022
    Location
    Kingston, ontario
    MS-Off Ver
    using open office and google sheets
    Posts
    13

    Re: Share Price lookup from Google Finance doesn't always work

    Quote Originally Posted by Haluk View Post
    P, Q, S, T and Y works for me but not O which is "Non-cumulative Rate Reset Class A Preferred Shares Series 39"

    I believe it's a problem related with Google server.

    Edit: You may use the following formula as well, which fetches the data from "google.com/finance"

    Your link removed so I could reply. (I am sure I have had almost 10 posts!)

    See my new problem above. There does not seem to be a consistent way to enter tickers even using both methods.

  10. #10
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Share Price lookup from Google Finance doesn't always work

    It looks like someone already gave you the BAM.PF.C ticker:

    https://support.google.com/docs/thre...sgid=191902901

    If that is not correct, can you explain why it is not correct?

  11. #11
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Share Price lookup from Google Finance doesn't always work

    I guess he wants to use a standard format for all tickers in "GoogleFinance" but, life is not so easy ....

  12. #12
    Registered User
    Join Date
    11-28-2022
    Location
    Kingston, ontario
    MS-Off Ver
    using open office and google sheets
    Posts
    13

    Re: Share Price lookup from Google Finance doesn't always work

    Quote Originally Posted by Haluk View Post
    I guess he wants to use a standard format for all tickers in "GoogleFinance" but, life is not so easy ....
    You are right.

    I already had the ticker I was "given". It is of course just the actual TSE exchange ticker. Not in the format that GOOGLEFINANCE specifies. And that exchange format does not work in GOGLEFINANCE for all the other tickers that include the usual PR.

    HALUK gave me a different way of accessing Google Finance Data using IMPORTXML. That worked, but I was concerned as to whether it still would still work if and when the website was re-designed, as they often are. Again, a different ticker format is required in IMPORTXML.

    To confuse things further, the format required when entering tickers into the Google Finance website is different again.

    To sum up, there is a problem with GOOGLEFINANCE's choice of format and instructions of how to enter a (preferred) share ticker. Only they can SOLVE this. We have to work around it.

    I have hundreds of tickers in spreadsheet. I wanted a single formula to convert the exchange ticker into a form that GOOGLEFINANCE could use.

    Not too elegant, but I did this as follows for preferred shares on TSE where A5 is the exchange ticker:
    =IF(LEFT(RIGHT(A5,4),2)="PF",A5,CONCATENATE("TSE:",LEFT(A5,FIND(".",A5) - 1),"-",RIGHT(A5,LEN(A5) - (FIND(CHAR(160),SUBSTITUTE(A5,".",CHAR(160),2))))))

    With that, my second problem was SOLVED. but needed the work-around.

    The original problem I asked about in looking up CM-O or CM-O:TSE is NOT SOLVED.

    Can we let this rest
    Last edited by Freeagent; 12-13-2022 at 10:22 AM.

  13. #13
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Share Price lookup from Google Finance doesn't always work

    is this not CM-O:TSE ?

    CM-O $17.50 (0.00%) Canadian Imperial Bank of Commerce Non Cumulative Rate Reset Pref Shs Class A Series 39 | Google Finance
    https://www.google.com/finance/quote/CM-O:TSE

    which means the googlefinance ticker is TSE:CM-O

  14. #14
    Registered User
    Join Date
    11-28-2022
    Location
    Kingston, ontario
    MS-Off Ver
    using open office and google sheets
    Posts
    13

    Re: Share Price lookup from Google Finance doesn't always work

    Quote Originally Posted by janmorris View Post
    is this not CM-O:TSE ?

    CM-O $17.50 (0.00%) Canadian Imperial Bank of Commerce Non Cumulative Rate Reset Pref Shs Class A Series 39 | Google Finance
    https://www.google.com/finance/quote/CM-O:TSE

    which means the googlefinance ticker is TSE:CM-O
    Did you read my post #1? Did you try using TSE:CM-O with the GOOGLEFINANCE function?

  15. #15
    Registered User
    Join Date
    11-01-2023
    Location
    rome
    MS-Off Ver
    2013
    Posts
    1

    Re: Share Price lookup from Google Finance doesn't always work

    i cant see anything

  16. #16
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Share Price lookup from Google Finance doesn't always work

    As an alternative to Google Finance, look at this link (which I use for tracking stock prices). It integrates nicely with Excel.

    https://www.michael-saunders.com/sto...ages/info.html
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  17. #17
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Share Price lookup from Google Finance doesn't always work

    pippopazzo you are responding to a thread that is almost a year old. If you have a question please post a new thread. As it is, we don't know what it is you can't see.

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #1 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] updating data in column price based on textbox doesn't work
    By KalilMe in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-12-2021, 08:59 AM
  2. Google Sheet-Googile Finance-Yesterday Price
    By grcshekar in forum Excel General
    Replies: 1
    Last Post: 10-29-2020, 08:56 AM
  3. [SOLVED] Google translation function doesn't work anymore? Please Help...
    By Rufles in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-14-2018, 11:48 AM
  4. Real Time Stock Price from Google Finance in Excel
    By chirag_patel5141 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-22-2016, 08:25 AM
  5. This code doesn't work when when i share the excel file ,,any hints
    By gostavee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2015, 05:13 PM
  6. VBA Doesn't Work When I Share a workbook
    By sforney123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2012, 06:59 PM
  7. Getting real time stock quotes from google finance / yahoo finance in excel
    By Navin Agrawal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2012, 12:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1