User Profile
Sam_S007
Copper Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Using Power Query Editor to Match Dates for Two Time Series
I have two different daily frequency time series (crude oil and the dollar index). But the dates don't match. I want to match the dates for them so I can plot them on the same graph. Can I use Power Query Editor to match the dates? I want to use all the dates for crude oil and just back fill data for the dollar index if they are missing. I attached a copy of the spreadsheet.781Views0likes0CommentsX-axis labels (Date) in Excel Chart
I'm trying to edit a single label on the X-axis of my Excel chart. I have a simple time series chart with date labels on the x-axis. I have chosen a single letter for the month. I want to edit the first month of the year (J) only and change it to 2020. The next label should remain F. I attached a copy of the Excel file and a screenshot of what the X-axis labels should look like.1.3KViews0likes1Comment- 1.8KViews0likes0Comments
Re: Excel IF Formula
BradD I tried using the IFERROR formula but there is still a problem. Column B returns a number. Sometimes that number is an error. Whenever it returns an error I want to use the previous number it returned (same column, previous row). So I wrote the following formula in the adjacent column: =IFERROR(B8,B7). But it doesn't seem to work. I attached the file below.1.9KViews0likes2CommentsRe: Picking out select data from a large table
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, Sam3.6KViews0likes4CommentsRe: Picking out select data from a large table
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! Sam3.6KViews0likes6CommentsRe: Picking out select data from a large table
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.3.6KViews0likes1CommentRe: Picking out select data from a large table
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!3.7KViews0likes11CommentsPicking 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. SamSolved3.9KViews0likes13CommentsMonthly vs Weekly Bar Charts
I have a simple bar chart for monthly frequency data that looks great. I have a separate bar chart for weekly frequency data that looks terrible because the bars are too thin. Why is the width of the bars different for monthly and weekly bar charts? How do I edit the weekly bar chart so that the bars are the same width as the default monthly chart? I've attached a copy of the spreadsheet.Solved12KViews0likes3CommentsRe: Historical Stock Prices into Excel from Yahoo! Finance
SergeiBaklan Just wondering why the first column (the Date column) is recognized properly as a date column while the other 6 columns are only recognized as text? Is there any way to change the format of the price columns from text to numbers without VBA or any programming? Thanks!13KViews0likes3CommentsHistorical Stock Prices into Excel from Yahoo! Finance
I am using Power Query to automatically download historical stock price data from Yahoo! Finance into Excel. The URL is https://finance.yahoo.com/quote/QQQ/history/ It populates a worksheet with a year's worth of stock prices with the following columns: Date/Open/High/Low/Close/Adj Close/Volume. When I try to build a simple line chart using the data, the price column data is not recognized. So the Y-axis of the chart returns no values. I changed the format of the columns to "Number" but it doesn't make any difference. Does anyone have any solutions or workarounds. I attached a copy of the spreadsheet.Solved13KViews0likes5Comments
Recent Blog Articles
No content to show