Stock Market Names and Symbols

[Gasman]

I was hoping downloading was what you were doing. I run to run the code but I wasn't familiar with all your functions. Can you give me a hint about how to use it? If I can make it work, I can certainly make changes to suit my needs, if I have to. Thanks for following up on this.

Is that addressed to me?

I wrote this in Excel, but you should be able to modify. You will need to get your own key. Review their documentation as to which type of download you want. They have a few.

strAPIKey is a global variable
Then this is called with

    Call GetData(strTicker)

where I walk through each cell that has a stock ticker in it.

Basically, build a url with the correct key and ticker value, plus type of download.

Sub GetData(pstrTicker As String)
'
Dim strConnection As String


'strConnection = "https://www.alphavantage.co/query?function=TIME_SERIES_WEEKLY&symbol=" & pstrTicker & "&apikey=" & strAPIKey & "&datatype=csv&outputsize=compact"
' Amended to one day figure as getting out of memory errors 260620
strConnection = "https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=" & pstrTicker & "&apikey=" & strAPIKey & "&datatype=csv&outputsize=compact"

    Debug.Print strConnection
    'Application.ScreenUpdating = False
    Sheets("Data").Select
    Cells.Clear

    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strConnection & "" _
        , Destination:=Sheets("Data").Range("$A$1"))
        .Name = ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[arnelgp]

yahoo finance no longer supports the urls I send

can you share the url?

 

[arnelgp]

I run to run the code but I wasn't familiar with all your functions. Can you give me a hint about how to use it? If I can make it work, I can certainly make changes to suit my needs, if I have to. Thanks for following up on this.

it is getting data from yahoo.finance.
for a test, open jsonData table and delete all records.
open module modUsingJson.
click inside GetYahooData2() sub and press F5 to run.
there is no message if it is done (todo).
goback to jsonData table and refresh (if already open) or open it
to see new data.

/Edit: just tested it today and is still working.
btw InternetExplorer app will not work anymore (maybe that's what you mean).
MSXML2.XMLHTTP is the way to go.

 

[Gasman]

I just tried it and it works for me also?
I even tried one of my London stock tickers?

Nice work @arnelgp

 

[Isaac]

++1

For all I make fun of Yahoo over the years, hey - they have been the ones consistently providing stock and financial data to the common man, those people wanting to create simple Excel downloads- and data connections-for years. They've kept it open and accessible and not overly complicated and I've noticed that with great joy in cases where I find so many websites with "too much information".

 

[Gasman]

@arnelgp
Why did you use Unix date and not a normal date ?

 

[arnelgp]

Why did you use Unix date and not a normal date

that was the Orig OP has and that is being returned from the website too.
anyway, there is a function to convert it the access date if you wanted to.

 

[Gasman]

that was the Orig OP has and that is being returned from the website too.
anyway, there is a function to convert it the access date if you wanted to.

Yes, I saw both the functions, but was curious as to why it was a Unixdate, that was all.

 

[theDBguy]

Yes, I saw both the functions, but was curious as to why it was a Unixdate, that was all.

Hi. Pardon me for jumping in... But I think it could be because most web servers are unix based. Or, at least, it started out that way, so it's probably all over the place now (even on Windows servers). Cheers!

 

[Gasman]

@arnelgp
OK, I tried to modify GetYahooData2 to allow for a stock ticker, and start and end dates.

Howvever the JSON string does not come back fully, and I get an error ') expected'

I cannot see what I have done wrong, I did try dates in mm/dd/yyyy format as well as mine is normally dd/mm/yyyy.

Could you possible tell me my mistake please Arnel ?
TIA

Edit: OK, sussed it. Need to take at least 1 day off today.
Plus in testing found that some entries can be null? , so surrounded the code with ISNULL()
Also in trying to debug, I took my own advice , and converted to string variables to see what was being produced.
I also had an error stating that data was not ready, so added a delay with code I found via Google.

Hope this helps others.

This is just the above mentioned mods to @arnelgp excellent code, a lot of which I still do not understand.

    stock = pstrStock
    If pdteFromDate = 0 Then pdteFromDate = DateAdd("d", -1, Date) ' Needs to be at least 1 day range
    If pdteToDate = 0 Then pdteToDate = Date
   
    startDate = CStr(toUnix(pdteFromDate))
    endDate = CStr(toUnix(pdteToDate))

    strOpen = "https://finance.yahoo.com/quote/" & stock & "/history?period1=" & startDate & "&period2=" & endDate & "&interval=1d&filter=history&frequency=1d&_guc_consent_skip=" & GetCurrentUnix(Now()) & ", False"
    Debug.Print strOpen
    With xhr
        .Open "GET", strOpen '"https://finance.yahoo.com/quote/" & stock & "/history?period1=" & startDate & "&period2=" & endDate & "&interval=1d&filter=history&frequency=1d&_guc_consent_skip=" & GetCurrentUnix(Now()), False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .send
        Pause (1) ' Sometimes get message data not available yet?
        s = .responseText
    End With
    Set xhr = Nothing
    If InStr(1, s, "Content is currently unavailable") <> 0 Then
        MsgBox "Content is currently unavailable"
        GoTo exit_here
    End If
    'WriteToText Environ("userprofile") & "\desktop\yahoo.txt", s
    head = """HistoricalPriceStore"":{"
    tail = "}]"
    jsonString = "{" & Split(Split(s, head)(1), tail)(0) & tail & "}"
    'Debug.Print jsonString
   
    Set json = ScriptEngine.Eval("(" + jsonString + ")")

    Set db = CurrentDb
    ' delete previous data
    db.Execute "delete * from jsonData;"
   
    For i = 0 To ScriptEngine.Run("N", json.prices) - 1
        With json.prices.element(i)
'           db.Execute
            If Not IsNull(.element("open")) Then  ' some entries can be Null :-(
                strSQL = "insert into jsonData ( " & _
                        "unixDate, " & _
                        "[date], " & _
                        "open, " & _
                        "high, " & _
                        "low, " & _
                        "close, " & _
                        "volume, " & _
                        "adjclose,  " & _
                        "StockTicker) " & _
                        "select '" & .element("date") & "', #" & _
                        Format(fromUnix(Val(.element("date"))), "mm\/dd\/yyyy h:n:ss") & "#, " & _
                        CDec(.element("open")) & ", " & _
                        CDec(.element("high")) & ", " & _
                        CDec(.element("low")) & ", " & _
                        CDec(.element("close")) & ", " & _
                        CDec(.element("volume")) & ", " & _
                        CDec(.element("adjclose")) & ", " & _
                        "'" & stock & "';"
                'Debug.Print strSQL
                db.Execute strSQL
            End If
        End With
    Next

Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause

    Dim PauseTime As Variant, start As Variant

    PauseTime = NumberOfSeconds
    start = Timer
    Do While Timer < start + PauseTime
    DoEvents
    Loop

Exit_Pause:
    Exit Function

Err_Pause:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
    Resume Exit_Pause

End Function

 

[Gasman]

@arnelgp
Ok, I am not trying to hijack this thread, but this could help others in the future.

I have now got the parameters working to provide correct data.

However at present it seems it has to be a yearly list?
If I just select a few days, or just the date, then it falls over, with what seems Bad Request?

So I am asking what needs to be changed to allow for ANY date range please?

Edit: Fixed with the Edit in previous post