时间戳

首页 > 解决方案 > 在 Yahoo Finance 上将 unix 时间戳调整为相同

excel - 在 Yahoo Finance 上将 unix 时间戳调整为相同

问题描述

在 Sheet1 中，我在 B1 中有代码（比如“AA”），在 B2 中有开始日期（比如 21/4/2009），在 B3 中有结束日期（比如 23/4/2009）

当手动导航到所需的网址时，我得到了这样的链接 https://finance.yahoo.com/quote/AA/history?period1=1240290000&period2=1240462800&interval=1d&filter=history&frequency=1d

但是当使用代码来构建链接时，我得到了一点不同的 UNIX 时间戳，例如 https://finance.yahoo.com/quote/AA/history?period1=1240272000&period2=1240444800&interval=1d&filter=history&frequency=1d

注意两个链接中的 period1 例如如何调整代码以与雅虎的链接相同？

我试过类似的东西

period1 = ToUnix(.Range("B2").Value & " 05:00:00")

这解决了这些日期的问题，但没有解决其他不同的日期，所以我的逻辑不正确

这是我尝试过的代码

Sub Yahoo_Finance()
Dim ws          As Worksheet
Dim sURL        As String
Dim sTicker     As String
Dim period1     As Long
Dim period2     As Long
Dim r           As Long

Set ws = ThisWorkbook.Worksheets("Sheet1")
r = 6

With CreateObject("MSXML2.ServerXMLHTTP")
    With ws
        sTicker = .Range("B1").Value
        period1 = ToUnix(.Range("B2").Value & " 05:00:00")
        period2 = ToUnix(.Range("B3").Value & " 05:00:00")
    End With

    sURL = "https://finance.yahoo.com/quote/" & sTicker & "/history?period1=" & period1 & "&period2=" & period2 & "&interval=1d&filter=history&frequency=1d"

    Debug.Print sURL
End With
End Sub

Public Function ToUnix(dt) As Long
ToUnix = DateDiff("s", "1/1/1970", dt)
End Function

标签： excelvbayahoo-finance

解决方案

据我观察，它从指定日期前一天晚上 11 点开始运行，到结束日期前一天晚上 11 点。因此，DateAdd代码中的 -1 天从工作表中的日期中删除 1 天并确保时间为23:00:00. 然后网址匹配我。

Public Sub Yahoo_Finance()
    Dim ws          As Worksheet
    Dim sURL        As String
    Dim sTicker     As String
    Dim period1     As Long
    Dim period2     As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With CreateObject("MSXML2.ServerXMLHTTP")
        With ws
            sTicker = .Range("B1").Value
            period1 = ToUnix(DateAdd("d", -1, .Range("B2").Value) & "23:00:00")
            period2 = ToUnix(DateAdd("d", -1, .Range("B3").Value) & "23:00:00")
        End With

        sURL = "https://finance.yahoo.com/quote/" & sTicker & "/history?period1=" & period1 & "&period2=" & period2 & "&interval=1d&filter=history&frequency=1d"

        Debug.Print sURL
    End With
End Sub

对于 GMT 本地时间转换，您可以尝试Rick Rothstein的代码 

Function Local2GMT(dtLocalDate As Date) As Date
    Local2GMT = DateAdd("s", -GetLocalToGMTDifference(), dtLocalDate)
End Function

Function GMT2Local(gmtTime As Date) As Date
    GMT2Local = DateAdd("s", GetLocalToGMTDifference(), gmtTime)
End Function

Function GetLocalToGMTDifference() As Long
    Const TIME_ZONE_ID_INVALID& = &HFFFFFFFF
    Const TIME_ZONE_ID_STANDARD& = 1
    Const TIME_ZONE_ID_UNKNOWN& = 0
    Const TIME_ZONE_ID_DAYLIGHT& = 2
    Dim TimeZoneInf As TIME_ZONE_INFORMATION
    Dim Ret As Long
    Dim Diff As Long
    Ret = GetTimeZoneInformation(TimeZoneInf)
    Diff = -TimeZoneInf.Bias * 60
    GetLocalToGMTDifference = Diff
    If Ret = TIME_ZONE_ID_DAYLIGHT& Then
        If TimeZoneInf.DaylightDate.wMonth <> 0 Then
            GetLocalToGMTDifference = Diff - TimeZoneInf.DaylightBias * 60
        End If
    End If
End Function

例如（根据 OP 反馈.. 不需要 DateAdd 调整和）

period1 = ToUnix(Local2GMT(.Range("B2").Value))

推荐阅读