I have a live data feed of stock prices in a sheet. The price for a security changes every 15 seconds. When the the price of an individual security hits a certain value, I want to add the date/time to another field and freeze the date/time at the moment the price threshold is hit. So, if security goes from $5 to $10 and $10 is the trigger, I am trying to collect and store the date/time the security hit $10, Any help appreciated.
Would need @Darren_Murphy or @gvalero’s GScript expertise here, but my idea is running a script that is triggered every minute, then:
- If “freezing timestamp” column is not empty then stop the script.
- If price >= trigger then write the current timestamp to the “freezing timestamp” column.
I agree with Thinh’s idea, that is the way.
Maybe you can have a deadband based on a raw value (price) or percentage (%) to get a more efficient routine.
Also, pay attention with daily total execution time of your script running every 1 min, Google just gives you 90 min every day (free).
Saludos!
1 Like
That logic seems sound, but if the data is updating every 15 seconds, then with a 1 minute trigger you only have a 1 in 4 chance of catching the correct time. @gp9293 how accurate does it need to be?
If it needs to be accurate to the second, then there are two things I could suggest.
- Firstly, how is the data getting into the sheet in the first place? I’d explore the option of making this check as the data is being updated.
- Secondly, can you use an onEdit() trigger? I know these don’t work with Glide updates, but it might work in your case. It’s something you’d need to test.
I think the first option would be best, but you haven’t provided enough information for me to say if that would be feasible or not.
1 Like
Updates read from an external database and update the price every 15 minutes, not 15 seconds (my mistake). So, when the price of a security is updated AND it exceeds a certain price (data for the trigger price is stored in another cell), then i want to write the date/time to a cell and not have the date/time change, even if the price of the security drops below the trigger. Let me know if this helps.
My question was more about the mechanics of getting the data in there. eg. is there an existing script that updates it? If yes, I’d be looking to modify that and include the extra logic.
Anyway, the approach suggested by @ThinhDinh should work fine. You just need to keep in mind the quotas mentioned by @gvalero
1 Like
Thanks Thinh.
Darren is right. A On-Edit trigger makes the most sense. You will get 4 events every hour and you can use those with their timestamp. If you are concerned with space you can always use a script that removes duplicate values as well to minimize the rows.
Have it working. Thank you all for the help.
1 Like
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.