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 ?
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 On the "popularity" tab, column C extracts the Time from column A. And the next extracts the Date. In E, I calculate the time for 20:45 minus the time value in C. Any positive value shows as hh:mm. However, when the Time is greater than 20:45 it becomes negative and displays as ########. In F, I check if the value on the next row in E is negative. That means that the time stamp is after 20:45. If so, then return 1, otherwise 0. In G, I find the User number if E is greater or equal to zero AND F equals 1. Thus, the nearest User value on or before 20:45. In H, I lookup the closing price for AAL for the date in D. #NA! means that there is no price, hence a week-end or public holiday.
The graph is then created on columns D, G (filtering out banks first) and H ("Connect data points with a line" so that you don't have to worry about deleting week-ends/holidays) with "Users" on the secondary axis.
All of this could be done with Power Query, and a chart based on the outcome. But it would take some learning, if you are not familiar with it. But the advantage would be that, once set-up correctly, you can use it for every consistently structured data set.
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.