Sample database retrieving Yahoo Finance info with WinHTTP into an Access table

[EricRex]

This modified version of the original project gets Date, Open, High, Low, Close, Adjusted Close, and Volume for a fixed date range set in Private Sub btnGetYahooInfo_Click().
The symbol list is more or less the same, but not sure how it would work if you selected more than one symbol. Haven't tested it but I suspect it won't work well.
I added the start and end date boxes but didn't add any VBA code to back them up since I'm new to VBA and Access.
I know the daily data retrieval works but I'm not sure others will work. Haven't tested it.

Sorry for the hack and slash code changes, but I hope it helps. I plan to continue to work on it and will have an updated version at some point in the future.
Thanks to orange for the great starting point.



DemoYahFin_updated.zip

[orange]

EricRex,

Looks good. Glad you are pursuing this demo/project. Please keep me updated as you proceed.

Tried it just now

===Starting New Session Ticker===07-May-24 7:41:16 AM==================================

Ticker symbols selected is/are AAPL
bTickersAcceptable True
-Requested fieldnames QuoteDate,Close
-Requested flddataTypes Date,Currency
Parm string matching these fields for Yahoo <DateClose>
Going to procedure StatusTblYahooFin to process fields and local table info
Entering StatusTblYahooFin with
-Fields -QuoteDate,Close
-Datatypes -Date,Currency
Current Table fields count is 7
Requested field count is 2
Field count in existing table does not match fieldcount in current request
so must create tblYahooFin with latest parms/fields
drop the table then
call the create table routine with latest fields
tblYahooFin deleted
Entering createFinTable
finalsql-- Create Table tblYahooFin( QuoteDate Date , Close Currency )
new table created
YahFin tbl exists
bFieldsAcceptable True
============== Entering btnGetYahooInfo_Click at: 07-May-24 7:41:31 AM
Set up URL and send request here

[Welshgasman]

AM I missing something here?
The tbl is populated with values, but no ticker, so cannot relate to what was selected?

ALso managed to get this error?

[orange]

Paul, EricRex

I did some testing based on Paul(WelshGasman) comment. I tried various combinations and got similar error message. I then added GSK to StockSymbolsT and found that I got successful run if I included all the Yahoo selections. Suggest further testing to resolve the issue.

General comments for EricRex: **I just reread your earlier post and see you have a fixed date range and you allow only 1 stockSymbol.

These were original comments.
-I requested Start 1-May-24 and End 7-May-24 Daily values for Stock GSK. I received 84 records with appropriate fields/values but included April . The April 10 record is number 67, so I had results for most of year-to-date. I don't think the code is respecting/using the Start and Stop dates??
Update: I checked tblYahooFin and I have records from Jan 4 2024 to May 7 2024.

-I did add a debug.print trying to see what might be causing error reported by Paul.

390       Debug.Print "k: " & k & "  rs.Fields(k): " & rs.Fields(k) & "  ArrRecs(k): " & ArrRecs(k)

I have attached the last immediate window printout. It only provides the last 100 lines or so. I don't think it's of much use. My thought is that the fields in the table and the fields selected must be in conflict somehow?? I could not get successful run if I chose a subset of possible selections??

-I recommend you include the StockSymbol (or FK) in the tblYahooFin data as Paul noted.

-I could not get multiple StickerSymbols selected??

[orange]

EricRex, Paul

A little more testing;

I added Symbol to the StockSymbolsT and this code in the processing logic

Dim symbolSQL As String
 
        symbolSQL = "Update tblYahooFin Set symbol = '" & Me.tbxTickSymbols & "';"
        CurrentDb.Execute symbolSQL, dbFailOnError
        If bYShowDebug Then Debug.Print vbCrLf & "**Populated tblYahooFin with Symbol: " & Me.tbxTickSymbols

.

I now can select the symbol and put it in the tblYahooFin.

[Welshgasman]

I had only a short time to look at it, as I was going out again in the community van.
I noticed that the dates were not taken into effect as well?

So I amended quickly as

Dim BeginDate As Long
BeginDate = DateDiff("s", "1/1/1970 00:00:00", CDate(Format(Me.txtStartDate, "dd/mm/yyyy")))
Dim EndDate As Long
EndDate = DateDiff("s", "1/1/1970 00:00:00", CDate(Format(Me.txtEndDate, "dd/mm/yyyy")))

I appreciate that it is still a WIP, and the concept is there and proven, so thank you for that.

I have an Excel workbook, that I use each Friday to get various stocks and import into quicken, which I would have offered, but I thought you were looking for more than stocks?
For that I use https://www.alphavantage.co/ but you have to register for an API key, which is free,

I caould also get GSK afterwards.

[Welshgasman]

I would use the same logic that you use for the Selections and then split it and loop for each value in the split?

I would also prefer the ticker symbol as the first field, so have amended code as

mSQL = "Create Table tblYahooFin( TickSymbol Text , "

290           rs.AddNew
            rs.Fields(0) = Me.tbxTickSymbols
300           For k = 0 To UBound(ArrRecs)
              ' Assign the incoming record fields to the recordset to add to the DB table
                  rs.Fields(k + 1) = ArrRecs(k)
340               DoEvents
350           Next k
              ' Update the table record
360           rs.Update

HTH

[Welshgasman]

@EricRex
Not sure if you are planning on using UK stocks, but they have a .L suffix, so GSK in the UK is GSK.L and the amount is in pennies (your cents), so need to be divided by 100 for actual price.
One could allow an option to choose the UK Yahoo site as well.

How does one get the line numbers in there?
FWIW I hate them , but how does one renumber as in the case where I place additional code lines?
I have never used line numbers in Access.

EDit: To that end with stock in UK values, I quickly added

290           rs.AddNew
            rs.Fields(0) = Me.tbxTickSymbols
300           For k = 0 To UBound(ArrRecs)
              ' Assign the incoming record fields to the recordset to add to the DB table
                  ' Allow for UK stock market
                  If Right(rs.Fields(0), 2) = ".L" Then
                        If Not IsDate(ArrRecs(k)) Then
                            rs.Fields(k + 1) = ArrRecs(k) / 100
                        Else
                            rs.Fields(k + 1) = ArrRecs(k)
                        End If
                End If
340               DoEvents
350           Next k

[Welshgasman]

You can also just use this for the timestamps

Dim BeginDate As Long
BeginDate = DateDiff("s", "1/1/1970 00:00:00", Me.txtStartDate) 'CDate(Format(Me.txtStartDate, "dd/mm/yyyy")))
Dim EndDate As Long
EndDate = DateDiff("s", "1/1/1970 00:00:00", Me.txtEndDate)  'CDate(Format(Me.txtEndDate, "dd/mm/yyyy")))

However I am not picking anything up after 03/05/24 despite putting 11/05/24 as end date.
I would have thought I would get todays values?

[orange]

Guys,

Back from golf and looking at the thread.

I modified table YHistQuoteParams to put the Symbol as the first record. This puts the Symbol to the leftmost position on tblYahooFin.

ID	YParm	YFormat	YMeaning
1	Symbol	Text(10)	Symbol
2	Date	Date	QuoteDate
3	Open	Currency	Open
4	High	Currency	High
5	Low	Currency	Low
6	Close	Currency	Close
7	AdjClose	Currency	AdjClose
8	Volume	Number	Volume

Because the Symbol is not part of the returned records from Yahoo Finance, I adjusted the code to populate tblYahooFin as follows:

360           rs.AddNew
370           rs.Fields(0) = Me.tbxTickSymbols
380           For k = 1 To UBound(ArrRecs) + 1
                  ' Assign the incoming record fields to the recordset to add to the DB table
390               rs.Fields(k) = ArrRecs(k - 1) '****needed to keep records received and added to tblYahooFin in sync
400               Debug.Print "k: " & k & "  rs.Fields(k): " & rs.Fields(k) & "  ArrRecs(k-1): " & ArrRecs(k - 1) 'sample print for test
410               DoEvents
420           Next k
              ' Update the table record
430           rs.Update

440           If bYShowDebug Then Debug.Print vbCrLf & "---record(" & i & ") from Yahoo Fin inserted into local table---" & vbCrLf  'for immediate window


450       Next i
' This is to show user that this TickSymbol was processed
460       If bYShowDebug Then Debug.Print vbCrLf & "**Populated tblYahooFin with Symbol: " & Me.tbxTickSymbols

I also added Paul's code for the Date to make use of the Start/EndDate parms. It works great.

I did use the Append option with different Symbol selections and Paul's date code.

I have received records for EWZ and GSK (Jan 4/24 to May 6/24) and AAPL (Jan 2/14 to May 3/24) and SPY (May 1/24 to May 3/24)
Total records for these 3 selections is 2775.

RE Line Numbers: I use MZTools and inserting/renumbering/deleting lines numbers is a selectable option.

Jack

[davegri]

RE Line Numbers: I use MZTools and inserting/renumbering/deleting lines numbers is a selectable option.

Ya know how line numbers started? Way back when, computers required data input on punched cards. No video data input or editing. One card held one line of code. The first 8 columns of each card was for the line number, and it was the responsibility of the person submitting the cards to the computer department to have the cards in order. Adding code required adding cards with sequential numbers between existing sequence numbers.
The computer DID NOT sort the card input for you. That was not its job. The sequence numbers were for YOU to keep the cards in order after dropping the deck on the floor (it happened). The computer did report the sequence number in a printed report if compile errors occurred. (And the report could not be viewed on your terminal because there weren't any.)
I remember it like it was 45 years ago, and I submitted thousands and thousands of COBOL punched cards.

[orange]

Dave,
So true. And I did similar with PL/1, Fortran and others. I started work with gov't org in '72 and couldn't believe they ran production jobs from card decks. Many of the people then were talking about "wiring boards" -that was before my time. I recall one project where we moved those card decks to catalogued procedures.
The days you describe involved a lot more desk checking and testing of code before submission to keypunch. Jeeez 52 years went by pretty damn quick.
Some very interesting youtube videos on IBM 704, System 360, System 370 ... many by ComputerHistoryArchivesProject.
Also some great documentaries by Asianometry Computer Related

[Welshgasman]

Cards were before my time, but the disks were huge when I started.
By the time I finished they were using standard hard drives, that we use today?

The attachment shows the disk array one could use on the Honeywell DPS7

https://www.1000bit.it/js/web/viewer...#zoom=page-fit

https://www.pinterest.co.uk/pin/591801207254995577/

[isladogs]

Just out of interest, did anyone look at my live currency exchange ticker that I mentioned in post #15?
I ask as nobody has mentioned it since.

[EricRex]

Hey guys,

Surprised that so many people jumped on this so fast. Also grateful for the good code mod's.

I'm struggling with a couple of those changes from a real, production DB design standpoint. Adding the symbol to tblYahooFin is awesome for small projects, but if I wanted to do several symbols for several years it starts becoming a very large table that won't perform well on a normal PC.
Additionally, if I want to look at multiple timeframes, the problem becomes much worse. Adding the timeframe turns the table into a nightmarish hack.

I've been considering having a table per symbol per timeframe, named something like DIA_DailyQuotes, DIA_1minQuotes, and so on.

Another change I'm toying has to do with the lack of choices in the returned fields. Since you can't select which fields come back, there's no point in having a selection box for them. But, Yahoo has many other queries available including financial statements info and so on. The list I know of is here: https://stackoverflow.com/questions/...rl-not-working

Given that, it does make sense to have a selection box for which query you want to execute.

Thoughts?
Eric