Forum Discussion
Picking out select data from a large table
Hi,
I'm trying to build an overlay chart with two lines. One line tracks the number of Robin Hood users holding shares of AAL stock. The other line tracks the price of AAL stock.
I downloaded user data from RobinTrack but it's poorly organized. I'm trying to chart the number of daily users (weekdays only) over two years. RobinTrack provides hourly data and includes weekends.
RobinTrack's table has two columns: Date/Time and Number of Users. There are 24 rows for each day time stamped at hourly intervals. I only need one row for each weekday time stamped at 20:45 UTC. So I only need one hour per day and I don't need weekends at all.
The price data is downloaded from Yahoo Finance and is fine. But I need the data from RobinTrack and the data from Yahoo to line up in terms of the date. The date column has to match for both time series I'm plotting (number of users and price).
I've attached a copy of the spreadsheet and an image of what I would like the chart to look like.
Sam
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.
13 Replies
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.
