Welcome Stockaholics!

We are a new and fast growing financial forum! Sign up for free and let's talk stocks!

  1. Do you want to help develop this community? We are looking for contributions from investors and traders like you! What stocks do you follow? What is hot right now? Sign up and get in on the ground floor of the newest, fastest growing financial forum!
    Dismiss Notice
  2. You will notice a live chat widget on the right. Click in to join us and lets hear about how you nailed that last UWTI trade!
    Dismiss Notice

Google Sheets Help

Discussion in 'Ask any question!' started by bigbear0083, Jan 14, 2019.

  1. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    OK, so I wasn't if I should post this thread in here first, or just have it posted it to the Google Docs Support board.

    I suppose I can try both, but admittedly the Google Docs support forum has felt like a waste of time to post in of late. Honestly seems like a bunch of idiots who don't know shit about anything who respond to questions on there lol. It's like you get people giving you the runaround all the time, and you end up with absolutely nothing at all. Just a complete waste time if you ask me.

    So, guess I will try here first to see if anyone might be able to help me out with something. If not, then I'll just chalk this up to not being possible in Google Sheets.

    Alrighty, so getting right to my question here!

    I'm wondering if in Google Sheets, is it possible to somehow freeze a cell that has the GoogleFinance formula in it?

    So say for example, I have the GoogleFinance price formula in a cell-

    PHP:
    =GoogleFinance("AAPL""price")
    This is a very simple price formula in Google Sheets that will pull the live price for a stock (AAPL in this example). The price will then automatically get updated about every minute or so (albeit not in real-time like in past years), but that's not what I'm after here.

    What I need to know is this.

    Is there any way to get price to not automatically update/fresh, preferably after the initial price entry into Google Sheets if you will?

    So, for an example sake -- say AAPL is trading 149.78, and when you input the GoogleFinance price formula into Google Sheets you get about the same price. However, what I'm after here is that I need that price entry into Google Sheets to stop updating after that initial entry...and I only need it for one cell, not for the entire worksheet!

    I think I know how this is done for an entire spreadsheet, but how about for just one particular cell that has the formula in it? Is this possible, or nah?

    If this is confusing you I don't blame ya! I'm pretty bad at trying to explain things lol.

    I'll just cut to the chase here, I'm basically trying to get Google Sheets to automatically enter the current price of a ticker, but not update price again after the initial entry.

    Seems like this trick failed me so now I'm wondering if someone might know how to do this.

    Haven't found anything in my searches, so I'm starting to think this is probably not going to be possible which is fine, but I just wanted to confirm just to be 100% sure.

    Thanks in advance! :)
     
  2. JerryM

    JerryM Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    705
    Likes Received:
    364
    Well leave it to me to send you down a rabbit hole :p I do know you have some scripting ability so I wonder if you can find what you want here: https://developers.google.com/apps-script/guides/sheets My guess would be that the formula could be contained within a script with a test of the destination cell for a value. If no value fire the formula if yes stop. Just a thought without an actual solution (I'm good at those) :p
     
    bigbear0083 likes this.
  3. Three Eyes

    Three Eyes 2018 Stockaholics Contest Winner

    Joined:
    Dec 14, 2017
    Messages:
    565
    Likes Received:
    498
    I've wracked my brain trying to think of a way to do this with formulas, and I'm drawing a blank. I think the problem is that the formulas in Google Sheets have no elegant way I can think of to store a variable, which is essentially what you want to do. Maybe this could be done somehow with a custom function, although with my limited Java/scripting skills I'm still not picturing how to get Sheets to store a variable.
     
    bigbear0083 likes this.
  4. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    Sorry! Late reply here. Never got the chance to say thanks for those links you guys posted in here.

    Yep that's exactly what I'm after @Three Eyes.

    So, I haven't yet had the opportunity to look into those links that you guys shared, but maybe I can work something out there.

    I am even looking into Google Forms to see if there's something I can work out there as well.

    It's crazy how much I've been using the Google Forms with Google Sheets of late, and it's really all credit to you @Three Eyes as I honestly woulda never even known about it if you hadn't ever brought it to my attention late last year. :eek:

    Hey BTW, is it just me or is anyone noticing a slight UI change in Google Sheets today?

    Looks slightly different today, albeit in a good way I think. :D
     
    Three Eyes likes this.
  5. Three Eyes

    Three Eyes 2018 Stockaholics Contest Winner

    Joined:
    Dec 14, 2017
    Messages:
    565
    Likes Received:
    498
    Yeah, Google has been rolling out some GUI changes across the entire suite of Docs, et al...as I understand from some articles I read last week, they are mostly aesthetic and not functional changes.
     
    bigbear0083 likes this.

Share This Page