Forum Discussion
Picking out select data from a large table
Sam_S007 If you ar not into PowerQuery, perhaps you find the attached file useful. I inserted a few extra columns to create the chart data and a separate chart that looks like the one you provided. The selected data points are all that are closest to but not after, 20:45 on any given day.
Sam_S007 See the attached.
I have added a new Sheet called "Result" containing both the aligned data for the Date, Price and Users. As well as a line chart.
Let me know if that's what you are looking for.
Hi, the number of users data is supposed to always select the daily row that is time stamped at 20:45 UTC. This isn't the case on the Results worksheet you created. Also the Results worksheet data stops in 2019 but needs to continue until June 15, 2020. I need step-by-step instructions on how you went about selecting the relevant rows from the user data worksheet and matching them up with the same days on the price data worksheet since I need to repeat this exercise for 100 different stocks.
Sam_S007 I think the problem is in this statement "daily row that is time stamped at 20:45 UTC"
For the whole of 2020, there is not a data point stamped at 20:45 UTC. The closest is 19:53:15 UTC and 20:48:34 UTC. How do you want to handle this ?
Even for previous years, the time aspect has seconds and not just Hour and Minutes, so by saying 20:45 UTC do you mean (a.) 20:45:00 UTC- 20:45:59 UTC ? or (b.) only 20:25:00 UTC. I went with a. Please specify ?
I use Power Query to handle this, i filtered the date present in the Price to only the dates present in Users. And I merge them together. I did every single thing using Power Query.