xlf | WS date - UNIX timestamp


UNIX timestamps are commonly used on web based data URL links. Consider the URL at row 2 of the worksheet in figure 1.
https://au.finance.yahoo.com/quote/BHP.AX/history?period1=1546300800&period2=1577750400&interval=1d&filter=history&frequency=1d.


This is a search string applied to Yahoo Finance, for BHP stock traded on the ASX, daily data.



xlf-yahoo-finance-unix-date-string
Fig 1: UNIX timestamps - are measured in the number of seconds since 1 January 1970

Excel date and time



UNIX date and time



Fig 2: Excel Web App #1: - 1. Convert WS date and time to UNIX timestamp, 2. Convert UNIX timestamp to WS date and time. The NOW function, rows 12 and 21 displays Pacific Time (the site of the XLF server)

UNIX ↔ WS conversion


From the example in figure 2, the WS formulas are:


URL string for Excel Web data link


xlf-yahoo-finance-unix-date-string
Fig 3: URL string - Advanced option for "From Web" item in the Data, Get and Transform data group - historical data (&events=history) at daily intervals (&intervals=1d)