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

Possible Changes Coming in 2019

Cy's official Stockaholics thread archives group.

  1. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    Haha. Well it almost worked. :D

    Seems like I found a small bug in the data dumping process.

    I don't have a lot of time to look at it now as I'm heading off in a little bit for holiday vaca but I'll have a closer look at it when I get back.

    The issue is that whenever someone submits the Google Form and the data gets dumped into an existing Google Sheet, the cell in the spreadsheet automatically get changes for whatever reason.

    For example, I have this formula which calls a cell in my Google Sheets template to where the data gets dumped in Google Forms-

    PHP:
    =Responses!B2
    So, "Responses" would be the Google Forms sheet, and "B2" would be the cell in my Google Sheet template where I'm asking Google Sheets to call the data from the Google Forms sheet if you will.

    However, whenever someone submits the form, "B2" in my Google Sheets template then gets changed to "B3" for whatever reason. Thus, you don't see any data input into my Google Sheets template. It's blank. I'm having to go in and manually change it back over to B2 which then shows the data fine, but that kind of defeats the purpose of making this automated haha.

    I'll look into this again when I get back, I don't think it's a difficult fix (or at least doesn't seem like it?).

    I'm not very familiar with how Google Forms and Sheets work together to be honest, this is all new for me. :p

    I'll see if I can dig up something on the Google docs support forums as well.

    Anyway, thanks again for sharing this amazing idea @Three Eyes seriously cannot say that enough!

    I honestly no idea that something like this could even be achieved.

    I said it before and I'll say it again, this will be a big time game changer for us going into 2019 if I'm able to pull it off. :cool:
     
  2. ApexC

    ApexC Active Member

    Joined:
    Nov 22, 2017
    Messages:
    229
    Likes Received:
    47
    Like I said before, I am not very good at sugar-coating. No offense if it comes out wrong or sounds harsh. I have worked on websites and I know first-hand the amount of time it takes. Your efforts do go unnoticed by the members that use your site regularly.

    When I first found your website I was looking for information I could use for my own real life portfolio. I was looking for a place that I could get ideas from other investors and information that I could use to make informed decisions on which stocks should look into purchasing.

    What kept me coming back was the daily pick contest and people playing this game. At the time the game was convenient. I could easily make my pick while I was preparing for my day. Once the rules changed it no longer became convenient. I continue to check in from time to time because I like the people. Your website makes feel like I am part of it and the members are my friends. It gives off a good online presence. This is something that is rarely found and a real credit to you and your efforts.

    As far as changes to the contests, I have my own ideas about that but if it does not fit into my daily schedule I likely won't be playing. At 12 o'clock at night my mind isn't on finding a good pick for tomorrows contest and getting it in for before 5am.

    If you ask each member "why do you use this website?" it may help you define your website and where you need to make changes. Once you know where than you can work on the what needs changing.

    I am starting to babble so I have stop while I am behind.

    I will talk later and have a good day.
     
  3. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    Hmm, I think I might have just found the correct formula to use to not have the cell references automatically change on my Google Sheets template on each new form submit. I guess we will see!

    If you all can do me this quick favor and just simply fill up this Google Form by clicking on the button down below-

    [​IMG] <-- click on that!

    ...and then here you can view your response in my sample Google Sheets weekly stock picking template-

    [​IMG] <-- click there to view!

    I'm thinking this is going to work, but I am not actually 100% sure as I haven't tested it out with more than 1 response.

    Please report back in here once you guys have submitted that Google Form and have viewed the Google Sheets link. Really curious to see if the new formula did the trick. Thanks!
     
  4. Three Eyes

    Three Eyes Well-Known Member

    Joined:
    Dec 14, 2017
    Messages:
    356
    Likes Received:
    313
    I submitted. It looks correct!
     
    bigbear0083 likes this.
  5. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    Thank you very much for the input here @ApexC I do greatly appreciate it.

    Hmm, well I am open to possibly reverting the deadlines for daily picks back to market open (9:30am eastern time) in 2019. However, honestly the one thing that is sort of pushing me back from going ahead with this change is my morning schedule. Unfortunately, unlike the early part of last year, I don't really get a whole heck of a lot of free time in the mornings before the market opens. The main reason that was changed to 4am eastern time during the middle part of last year was because it was much easier for me to input everyone's picks into my Google Sheet as soon as I got up bright and early at 5am eastern (which is typically when I start my day), so then I could just simply head out the door knowing that I didn't miss getting anyone's picks into my spreadsheet.

    If I changed the deadline back to 9:30am eastern time, then I would miss getting later picks in. It wouldn't be until later that I'd be able to add them in to my Google Sheet.

    I mean, I guess we can still try it anyway. But, I think it would be a little awkward having later picks entering the spreadsheet. In addition, a little frustrating to boot?

    I'll tell ya what though, if I can ever get a handle on that new automated picks idea that @Three Eyes had first brought to our attention in here, then perhaps this will not be any issue going forward, since I would not have to be present to input later picks into my spreadsheets. However, it is still in its early stages right now, and I'm just testing things out now.

    So far it seems to be going good, however I have not yet tested this out on the daily picking contest, only the weekly for now.

    Thank you again for the input in here @ApexC :)
     
  6. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    I just wanted to quickly send extend another HUGE thank you again @Three Eyes for bringing this idea to my attention last week! :cool:

    Everything seems to be working pretty good on my end it would seem, however, the only issue I think I would have with this new automated tool would be getting everyone's market direction into my existing spreadsheet.

    I suppose what I could do is just simply collect everyone's market direction votes via the actual "poll" itself on the thread, since we can see who voted for what when clicking on the number of votes on the poll. It lists all of the usernames who clicked in for each poll option (Up, Flat, Down).

    But, it most likely wouldn't be possible to use google forms for market direction, which is not really a big deal actually.

    I'll just use it for inputting members picks into the spreadsheet which is most important.

    Going to throw up a quick test of the daily picks contest in here as well since that's the one I'm really going to be needing this for.

    I'll post that up in here just as soon as I'm done setting it up.
     
    Three Eyes likes this.
  7. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    Alright here we go. This is just a quick test of my daily picking contest.

    If you all can do me this quick favor and just simply fill up this Google Form by clicking on the button down below-

    [​IMG] <-- click on that!

    ...and then here you can view your response in my sample Google Sheets daily stock picking template-

    [​IMG] <-- click there to view!

    Please report back in here once you guys have submitted that Google Form and have viewed the Google Sheets link.

    Thanks again!
     
    Three Eyes likes this.
  8. Three Eyes

    Three Eyes Well-Known Member

    Joined:
    Dec 14, 2017
    Messages:
    356
    Likes Received:
    313
    Looking good!
    I filled out the form and it showed up in the spreadsheet.

    But aren't you using Forms for automatic capture of the stock picks direction? Wouldn't it be the same process for mkt direction? Or perhaps nesting your market direction formula inside an IF statement that references the market direction cell? (Just thinking out loud, here. I'm sure it's not as cut-n-dry as I'm imagining here. o_O)
     
    bigbear0083 likes this.
  9. stock1234

    stock1234 2017 Stock Picking Contest Winner

    Joined:
    Apr 3, 2016
    Messages:
    3,220
    Likes Received:
    2,061
    OK I just filled out the form, looks like my pick is showing up :D
     
    bigbear0083 likes this.
  10. ApexC

    ApexC Active Member

    Joined:
    Nov 22, 2017
    Messages:
    229
    Likes Received:
    47
    I think you are heading in the right direction. When I made my pick though it did not record the price at the time of purchase.
     
    bigbear0083 likes this.
  11. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    Thanks much fellas for helping me test out this Google Forms idea!

    I think I am almost willing to give this a trial run on an actual daily competition. (Maybe tomorrow's!) :D

    I mean, I might as well put this in "action" before we actually decide to go live with it next year. :p

    The test form you guys filled out above seem to work pretty good. Everyone's names, stock picks and stock pick direction seemed to get input into my Google Sheets template exactly how I envisioned, without me needing to make any additional changes to the spreadsheet which is exactly what we're aiming for here!

    The goal here is to make these as "automated" as possible so I don't need to be around to setup the spreadsheet.

    If all goes as planned, then I think I would be very open to changing back the deadlines for getting in stock picks at market open (9:30am eastern) again.

    First things first though, I am going to test run the forms idea for Wednesday's daily contest and see how that goes.

    I think we'll get a better idea about all of this after tomorrow! ;)
     
    Three Eyes likes this.
  12. stock1234

    stock1234 2017 Stock Picking Contest Winner

    Joined:
    Apr 3, 2016
    Messages:
    3,220
    Likes Received:
    2,061
    Sounds good Cy :D Hope it will work out just fine :)
     
    bigbear0083 likes this.
  13. Three Eyes

    Three Eyes Well-Known Member

    Joined:
    Dec 14, 2017
    Messages:
    356
    Likes Received:
    313
    So I don't play the dailies, but I will this time to see how your set-up handles a newcomer. :)
     
    bigbear0083 likes this.
  14. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    OK, so I think we might need to do some more tinkering around with those forms. Yesterday's first ever test run on an actual daily contest kinda failed haha. Well, not really, but I think what I will need to do (and I'll try this for Thursday's comp) is to enable these 2 options in the forms-

    [​IMG]

    This way we don't have an instance of multiple picks being submitted to the automated Google Sheet from one single user, or that it can't be abused. I mean, technically it still can since someone can just sign up to multiple google accounts and do it that way, but I think that would be kind of a hassle, and anyway, why would anyone want to waste their time doing that? Would have be a total loser lol. :p

    Anyhow, I'm going to give this another test run on Thursday's thread with the above options enabled on the form. We'll see how that goes.

    One thing I'm really curious about though, since I haven't actually tested this out myself. I'm wondering that once someone has submitted the form, then closed out their web browser, can they still come back to their submitted pick via the form and edit it? I'm thinking yes, since I have the "required sign in to Google" option enable.

    Maybe I should test that out now and see if that is actually the case. I'm really curious about this. I think that would be a pretty cool feature if people can still edit their selection even after they have closed out their web browser. o_O

    Anyway, I think this forms format is looking very promising. I think we're getting closer and closer to nailing this thing down.

    Still need to some additional testing, but so far so good.

    All credit to @Three Eyes though as I honestly wouldn't have ever known about this new way of doing this. :cool:
     
    Three Eyes likes this.
  15. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    Great news y'alls! The answer to my above question is a resounding, YES!

    [​IMG]

    So, you can completely close out your web browser after submitting your pick via the Google Form, and then re-enter the form and you'll get hit with the message in my screenshot above. Simply hit the "Edit your response" link and that is where you can edit your pick. Awesome! That is precisely what I was after here.

    Keep in mind though that you MUST have a Google account (Gmail or whatever should work fine) to submit your picks via the Google Form. I'm very eager to give this a test run for Thursday's comp! :D
     
    Three Eyes likes this.
  16. Three Eyes

    Three Eyes Well-Known Member

    Joined:
    Dec 14, 2017
    Messages:
    356
    Likes Received:
    313
    If you run into additional snags, another option you could try is to require data validation for the name field in the Form. So, for example, you would make everybody's Stockaholics ID available in a drop down box in the Form. The downside is that a newcomer would have to request to be added, but that shouldn't be too big of a deal.
     
    bigbear0083 likes this.
  17. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    Amazing! @Three Eyes can I just say you are my hero! Haha. Holy cow I did not even think of that. I may try this on Friday's contest just as a tester to see how it goes. Super awesome idea! :cool:
     
    Three Eyes likes this.
  18. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    hmm, unfortunately, i don't think that is going to be possible with the current setup i am using...although i could be wrong. i think that would be amazing if there was a way to have google forms record the price of a members pick at the time a form is submitted. so for example, if someone picked AAPL - long at like 11:34am eastern, it would record AAPL's stock price at exactly that time in the spreadsheet as the starting point for the pick, as opposed to using the current day's opening price as the starting point. so then we could just abolish the "deadline" for picks altogether and allow for any member to join at any time during the trading day. but, i honestly have no idea if it is even possible.

    i'll take a good look though and do some testings and see what i can come up with because i think that would be a crazy awesome idea imo.
     
  19. ApexC

    ApexC Active Member

    Joined:
    Nov 22, 2017
    Messages:
    229
    Likes Received:
    47
    This is what I was hoping. I am unsure exactly how just how you are storing and transferring the data. Is the information being stored database or just being transferred to a spreadsheet. It looks like your using a freebird form and using this to transfer to a spreadsheet that you have made public. Would this be correct?
     
  20. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    13,432
    Likes Received:
    4,116
    Here's an idea!

    What if I make it so that people are required to post the entering price of their pick at the time that they are entering into the Google form as well?

    Then, I suppose we can check to see if that price is accurate to the time that they entered?

    Might be a little tedious, but it may actually be possible if I can get some help from other members as well to make sure those prices are indeed accurate.

    I think it's actually doable. But, not sure how you guys would feel?

    In fact, I have an idea...let me throw up a quick test sheet right now while I have some time this afternoon and test some of this out.

    I'd like to do this while the market is still open.

    Let's see if this would work.

    One moment! :D
     

Share This Page