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 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.
Hi Riny_van_Eekelen,
Would it be possible for you to build a template in Power Query (with a chart too) that I can then use for each of the 100 stocks that I need to analyze and chart? And would it be possible to remove the weekend rows as well? I'd be happy to pay via paypal for the template.
Thanks,
Sam
Sam_S007 Far from beautiful and perhaps not very useful either. But then, I am a beginner in the field of PQ. As a long-term Mac user I only recently started using PQ after installing Excel for Windows, on a virtual W10 machine on my Mac.
Anyhow, as long as each file has a two structured tables, named Users and Prices, with headers exactly as in the attached file, the queries should create a table with dates for weekdays, user numbers closest to but not later than 20:45 and the closing price for that day. The graph is linked to the output table and should also update automatically. All can be refreshed at once but pressing the Refresh button on the Data ribbon.
Since you indicated that you downloaded the data from a service called RobinTrack, you probably need/want a more dynamic solution that allows you to directly query the website data, pulling in, transforming and refreshing data automatically. Doable, but as I'm not an expert in this field (yet) I don't consider myself the right person to help you with that.
Hi Riny_van_Eekelen,
This is fantastic! You just cut my workload by 99%. This is going to make a huge difference.
Thanks very much this is a superb template that will get a lot of use.
Sam
Sam_S007 Glad it worked out!
Sam_S007 Will have to get back to you later.