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

[pbaldy]

Post 30 was moderated, I'm posting to trigger email notifications.

[Welshgasman]

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.

I did not Colin, but then I already have a version on my PC.
That is for currencies only though, is it not?
I already have a stock ticker excel workbook that I created for the few stocks I have, as I mentioned in a previous post.

[orange]

Eric,
We need some specifics. It's been years since I looked at any of this and then only helping someone else. The YahooFinance interface has changed. I have looked at your crumb process, but I don't know what fields are available from Yahoo. Previously I picked 6 or 7 and let user select any or all of those. Your current code doesn't allow that. Perhaps you can send me a private message so we can clarify what is available and what you need. I can't say we can get all you need, but we can adjust what you have to get closer to what you need -hopefully.

The original code allowed selection of multiple tickers, a common time frame and selection of 1 or more fields from the 6 or 7(of the many) that were available from YahooFinance.

Based on what fields were selected, there was a compare to the existing local tblYahooFin. If the fields selection had same structure as the tblYahooFin then you could Append the new records, or delete and rebuild. If the structure was different, then the system would delete the existing tblYahooFin and rebuild the table based on the fields selected for extraction.

[EricRex]

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.

Hey Welshgasman,
Too me a while to figure out why I was getting reams of data. Your date format works too well in the US. Switched it to:
Dim BeginDate As Long
BeginDate = DateDiff("s", "1/1/1970 00:00:00", CDate(Format(Me.txtStartDate, "mm/dd/yyyy")))
Dim EndDate As Long
EndDate = DateDiff("s", "1/1/1970 00:00:00", CDate(Format(Me.txtEndDate, "mm/dd/yyyy")))

But thanks for the update! Works great!

[EricRex]

Eric,
We need some specifics. It's been years since I looked at any of this and then only helping someone else. The YahooFinance interface has changed. I have looked at your crumb process, but I don't know what fields are available from Yahoo. Previously I picked 6 or 7 and let user select any or all of those. Your current code doesn't allow that. Perhaps you can send me a private message so we can clarify what is available and what you need. I can't say we can get all you need, but we can adjust what you have to get closer to what you need -hopefully.

The original code allowed selection of multiple tickers, a common time frame and selection of 1 or more fields from the 6 or 7(of the many) that were available from YahooFinance.

Based on what fields were selected, there was a compare to the existing local tblYahooFin. If the fields selection had same structure as the tblYahooFin then you could Append the new records, or delete and rebuild. If the structure was different, then the system would delete the existing tblYahooFin and rebuild the table based on the fields selected for extraction.

I think your message was superseded by recent events, but in the interface I implemented, you don't get to pick what fields are returned for historical quotes and, as far as I know, you can't ask for more than one symbol at a time. But now, the choices are different. You can pick different timeframes (e.g. bar length) for those historical quotes as well as several other kinds of data.

The crumb process is required as part of the HTTP session, but isn't relevant to the data being passed back and forth. I has something to do with a cookie or something similar.

Now, since the fields returned is standardized, the only reason to create a new table is for a different symbol, if desired, or to replace the data present.

Hope that clarifies.

[pbaldy]

Post 34 was moderated, I'm posting to trigger email notifications.

[orange]

Eric,
You don't necessarily need a different table for different Symbols or extractions.

If your requirement is to have all the fields you have in your fields listbox, then all returned data will be the same format. The difference would be in the TimeFrame and Start and End dates.

In the original demo you could have multiple Symbols, but if so, then the other parameters--fields retrieved and timeframe were probably fixed. I don't know exactly what fields are available for download. If you have a link to the site or the relevant info, I'd like to see it. If we knew what was available, then it would help with any decisions and logic.

I have modified a copy of your database to use:
--the date setup that Paul proposed
--the ability to select any one of the Timeframes
--append different Symbol data to local tblYahooFin
--a few more messages for progress through the process

I'll attach a zip file that includes a gif file showing the process. Open the zip and run/open the gif.

[EricRex]

Eric,
You don't necessarily need a different table for different Symbols or extractions.

If your requirement is to have all the fields you have in your fields listbox, then all returned data will be the same format. The difference would be in the TimeFrame and Start and End dates.

In the original demo you could have multiple Symbols, but if so, then the other parameters--fields retrieved and timeframe were probably fixed. I don't know exactly what fields are available for download. If you have a link to the site or the relevant info, I'd like to see it. If we knew what was available, then it would help with any decisions and logic.

That link was included in Post #30. Here it is again for convenience.

https://stackoverflow.com/questions/...rl-not-working

This is the best reference I have, but I haven't searched very far.

Cheers,
Eric

[pbaldy]

Post 38 was moderated, I'm posting to trigger email notifications.

[EricRex]

Hey there,

Not sure if this is a better link than the previous one, but FYI:
https://cryptocointracker.com/yahoo-...oo-finance-api

Cheers,
Eric

[EricRex]

Hey all,

After some more testing, I found that none of the intraday timeframes work as expected. They all return a 404 error from the HTTP request.

On the plus side, all of the daily and greater timeframes work just fine.

Cheers,
Eric

[Welshgasman]

Hey Welshgasman,
Too me a while to figure out why I was getting reams of data. Your date format works too well in the US. Switched it to:
Dim BeginDate As Long
BeginDate = DateDiff("s", "1/1/1970 00:00:00", CDate(Format(Me.txtStartDate, "mm/dd/yyyy")))
Dim EndDate As Long
EndDate = DateDiff("s", "1/1/1970 00:00:00", CDate(Format(Me.txtEndDate, "mm/dd/yyyy")))

But thanks for the update! Works great!

I upgraded to

    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")))

I would expect that would work for US format as well? as the base date is the same, be it in dd/mm or mm/dd format?