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.

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

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

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)


Fig 4: Web data - BHP.AX displayed in Table named BHP :: 2-Jan-2019 to 31-Dec-2019