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 file.
To start, I have added a new sheet/tab (New Number of Users) that gives you the total number of users per day(summed up per hour), and this is only for Mon-Fri(Excludes Sat and Sun).
For the AAL Price, there is a date column, and 6 other columns, which of these 6 columns contain the price you are looking to pair up with the users? I mean how do i get the price out of the table in AAL Price ?
- Hi, for the number of users data I only need one data point per day at 20:45 UTC. I don’t need the sum of all the users per day. Just a single row of data per day that is time stamped 20:45 UTC. For the price data I only need the “Close” column. That’s the fifth column.
Thanks!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.
Hi Riny_van_Eekelen,
This works and the chart is exactly what I'm looking for. Just wondering how you did it? How did you select only the rows at 20::45 UTC or closest for each day and how did you match them up with the corresponding rows for the price? I need to repeat this for another 100 stocks.
Also, would it be possible to remove the weekend rows entirely from the table? (So it's just Monday to Friday.)
Thanks!
Sam
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.
