Problem Updating URL with Unix Times

[Anonymous]

Hello all,

I am currently making a dashboard that pulls stock prices from Yahoo Finance with an end date of the current day and a start date of 30 days prior. I have figured out how to create a function to iterate over a list of stock symbols, but I am struggling to figure out how to make the dates in the URL change.

For example, the URL I am using is: 

https://finance.yahoo.com/quote/EEM/historyperiod1=1659052800&period2=1661731200&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true

I have already made the EEM/StockSymbol portion a function. In this URL, periods 1 and 2 are in Unix Time and I would like for them to update automatically to the date range explained above when refreshed. I think I need to use these two formulas, but I am not sure how to implement them with the query editor. Any advice is greatly appreciated!

Start Date: = Number.Round(Duration.TotalSeconds(DateTime.LocalNow() - #datetime(1970,1,1,0,0,0))-2592000,0)

End Date:  = Number.Round(Duration.TotalSeconds(DateTime.LocalNow() - #datetime(1970,1,1,0,0,0)),0)

[mahoneypat · Accepted solution]

Please try this syntax. You need to convert your start and end to text to concat them with the rest of the url.

let
    StartDate = Number.Round(Duration.TotalSeconds(DateTime.LocalNow() - #datetime(1970,1,1,0,0,0))-2592000,0),
    EndDate = Number.Round(Duration.TotalSeconds(DateTime.LocalNow() - #datetime(1970,1,1,0,0,0)),0),
    
    Source = Web.Contents("https://finance.yahoo.com/quote/EEM/historyperiod1=" & Text.From(StartDate) & "&period2=" & Text.From(EndDate) & "&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true")
   
in
    Source

Pat

[Anonymous]

Hello,

Thank you very much for your response! This was the solution!