Query filtering using Today() as a date ?

chuggins143

Board Regular
Joined
Nov 10, 2009
Messages
100
Hey all,

Okay, I'm pulling in stock market data... specifically dividend data which I can filter by dates. (example data... Eagle Point Credit Company Inc. (ECC) Dividend History, Dates & Yield - Stock Analysis) I can open the Power Query Editor and use the Date Filter on the Ex-Dividend Date column but I'm limited to only using specific dates... is there a way to filter "is before or equal to" Today()??

I'm new to the Power Tools so this is a learning experience for me. I'm not finding much out there so a point in the right direction would be appreciated.

Thank you,
C
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Power Query:
let
    Source = Web.BrowserContents("https://stockanalysis.com/stocks/ecc/dividend/"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(1)"}, {"Column2", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(2)"}, {"Column3", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(3)"}, {"Column4", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(4)"}}, [RowSelector="TABLE.svelte-1jtwn20 > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ex-Dividend Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Ex-Dividend Date"] < Date.From(DateTime.FixedLocalNow()) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"
 
Upvote 0
This also works.
Power Query:
let
    Source = Web.Page(Web.Contents("https://stockanalysis.com/stocks/ecc/dividend/")),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Source] = "Table")),
    Data0 = #"Filtered Rows"{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Data0",{{"Ex-Dividend Date", type date}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each [#"Ex-Dividend Date"] <= Date.From(DateTime.FixedLocalNow()))
in
    #"Filtered Rows2"
 
Upvote 0
I obviously did something wrong... I get this error... "Expression.Error: The name 'Html.Table' wasn't recognized. Make sure it's spelled correctly." The advanced editor tells me that there are no syntax errors so...
Power Query:
let
    Source = Web.BrowserContents("https://stockanalysis.com/stocks/ecc/dividend/"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(1)"}, {"Column2", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(2)"}, {"Column3", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(3)"}, {"Column4", "TABLE.svelte-1jtwn20 > * > TR > :nth-child(4)"}}, [RowSelector="TABLE.svelte-1jtwn20 > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ex-Dividend Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Ex-Dividend Date"] < Date.From(DateTime.FixedLocalNow()) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"
 
Upvote 0
I did get this to work as it's closer to what I'm seeing on my current queries... Follow up question, how can I add a few days to the Now()? So say I want it to be 3 days in the future? I tried just adding a "+3" to it, but the syntax didn't like that.

This also works.
Power Query:
let
    Source = Web.Page(Web.Contents("https://stockanalysis.com/stocks/ecc/dividend/")),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Source] = "Table")),
    Data0 = #"Filtered Rows"{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Data0",{{"Ex-Dividend Date", type date}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each [#"Ex-Dividend Date"] <= Date.From(DateTime.FixedLocalNow()))
in
    #"Filtered Rows2"
 
Upvote 0
You'd change
Date.From(DateTime.FixedLocalNow())

to
Date.AddDays(Date.From(DateTime.FixedLocalNow()),3)
Excellent! I have not tried that... Again, I'm still a bit new to the whole query side of Excel, so I'm not familiar with the syntax yet. I'm good with most Excel syntax and writing macros and such just not this yet. Is it based in some other language? Obviously, I have not taken the time to dig into it too deep... Thanks!
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top