Solved: Interfacing VBA-Excel and Yahoo finance

[cmagalha]

My knowledge of VBA is minimal. But I still need to code a small application to interface EXCEL and YAHOO finance, to retrieve data (quotes) for financial funds. I've got help to get this far:
[vba]
Sub OpenQuotes()
counter = 1
Do Until Len(Cells(counter, 1)) = 0
Workbooks.Open "$h$t$t$p$: //finance.yahoo.com/q?s=" & Cells(counter, 1)
'MORE CODE HERE, TO COPY DATA TO SPECIFIC CELLS
ActiveWorkbook.Close
counter = counter + 1
Loop
End Sub
[/vba]
This macro gets "tickers" from cel A1 and below, interacts with Yahoo (I can see the little window Yahoo opens during the process), but that is it.

Questions are:
1. how do I determine where the VBA code places the data collected from Yahoo and how do I move it to specific cells on a worksheet ?
2. How do I execute a previously written VBA macro from inside another one ?
Any help at all would be much appreciated. Thank you.
($ signals inserted to prevent link detection)

[10west]

1. You look at the code to determine that, so you will see values in some form and where they are going, so you have to post it, or look up the stuff in Excel VBA
2.

Sub MacroMacro()
Application.Run "Book1!Macro1"
End Sub
you can call more than a local macro with this, such as in otherworkbooks

[10west]

1b. After you know what it's doing, you can direct the data around anywhere. A basic hint is most stuff is going an x y trip up and down rows, across columns, etc, depending on the sequence needed, so you just transpose stuff, redirect, etc. Excel is pretty straghtforward in that department, you can access rows, cols, cells, ranges you need, and do what ever- it's just text, sitting in a matrix- in it's simplest form. I can get funky from there, but it's about a page of code, for just moving stuff around.

[mdmackillop]

Hi,
Welcome to VBAX
Can you post a sample of yor workbook with "tickers". Use Manage Attachments in the Go Advanced section.
Regards
MD

[Cyberdude]

I'm very interested in what you are doing since I also retrieve data daily from Yahoo Finance. I get mine at the end of the day when the market is closed. I put a list of all my securities in what they call a "portfolio", then I click on the area that says "download to spreadsheet". It creates a ".csv" file, which I use Excel to manipulate. This is quite crude, but I've been doing it for years. It avoids all the timing issues that extracting real-time data can incur. Actually what I do can be done at any time of the day, but the data during the day is delayed about 15 minutes.
What are you looking at when you extract your data? Is it a real-time system of some sort?

[cmagalha]

Thank you all.

Cyberdude: Iam also using this method for more than an year, but is is really time-consuming. And I am also tracking about 1.000 ETFs, on a weekly basis, to identify the best performing. Terrible !
Aswering your question: I get quotes twice a month, after the market closes.

10west: thank you for the answer. I did not understand the fist part, but the second part wil lbe very useful.

mdmackillop: I will post a sample worksheet (TESTE.xls), showing the code plus a few tickers. Just for the record, a few: EWW, APB, EWP, EWZ, IFN. Idealy, the task would vary on size: could be just one ticker, or one thousand of them, retrieved one-by-one. The VBA code would pick each a time, from a list preloaded somewhere (away from the code itself).

I tryied to add some code for saving the contents, but found out that the returned data is NOT what I am looking for. What I need is a complete se of quotes, like the set we can download bu clicking the link "download to spreadsheet" on the Yahoo finance results page. The returned data is only the "initial page" for the particular ticker.

On a second workbook I have added some URLs proved to work when used on a browser. They will return the needed data, which can be downloaded as described above.

Thank you for any additional help.

[mdmackillop]

This is a bit out of my area, I'll have another attempt with this later, but I'll reply again to get you to the top of the list!

[Charlize]

I've noticed that for the csv thing it's just an url. Maybe we can do something with an instance of internet explorer. If you navigate to a webpage the browser opens the page. In this case, when the link is in a cell and you click on it, all the data is transferred to a sheet.

http://ichart.finance.yahoo.com/tabl...=d&ignore=.csv

Charlize

[cmagalha]

Charlize
Thank you for the answer.
When I enter the statement above into IE, it works fine. An Excel instance is loaded inside the browser and the CSV file is loaded into it. This is the regular way for getting data from Yahoo Finance. After that, I mark all nedded cells and Copy/Paste into my own worksheet.
What is neded is a way for automating the whole process, getting data, copying and pasting into the private worksheet (and executing some formatting VBA macros during the process). And the reason for this is that I have, at least, 30 tickers to analyse. Doing by hand each and all of them is very time consuming and leads to errors.
Thanks !

[Charlize]

try this one :
[VBA]Sub tickertwo()
Workbooks.Open ("http://ichart.finance.yahoo.com/tabl...=d&ignore=.csv")
End Sub[/VBA]

Is it this what you're after ?

Charlize

[cmagalha]

Thank you.
I was able to figure this out:
=======================
Sub GetQuotes()
counter = 1
Do Until Len(Cells(counter, 1)) = 0
ticker = Cells(counter, 1)
Workbooks.Open "LINK-GOES-HERE" & Cells(counter, 1) & "&d=10&e=10&f=2006&g=d&a=3&b=1&c=1996&ignore=.csv"
ChDir "C:\Mdocs\ADRsETFs\TESTES"
ActiveWorkbook.saveas Filename:=ticker & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Close
counter = counter + 1
Loop
End Sub
===================================================
LINK-GOES-HERE: xxxxxx://ichart.finance.yahoo.com/table.csv?s=
===================================================

Using this code I am able to retrieve data from Yahoo (in this case, daily quotes) and have them loaded to PLAN1 into a new workbook and SAVED.

The LOOP allows me to get data from several tickers, aligned on cell A1 of PLAN1 sheet.

It is a miracle, given my knowledge of VBA. However, I need more.

I need to download monthly and weekly data, and I assume that similar code would do it.
But I also need to download this data into a "model" workbook, where some macros would format the downloaded data according to my needs. This workbook has three sheets (monthly, daily and weekly).

Questions here are:
- How do I select an existing workbook (MODEL.xls) to be target for a download ?
- How do I select a specific sheet within that MODEL.xls ? (PLAN1, PLAN2, etc)
- How do I point to a specific cell for data to be downloaded into ? (A4, as example)
- How do I run an existing VBA MACRO against that data (a few macros for each sheet) ?
- How can I input other parameters to the above VBA macro, to modify the harcoded dates: &d=10&e=10&f=2006&g=d&a=3&b=1&c=1996


Many thanks for any help. This seems to be more clear now !!

[Charlize]

And what about this one. Uses the date of the computer to fetch stockdetails. Hope this will help a little.
[vba]Sub tickertwo()
Dim stock As String
Dim stringstock As String
Dim V_day As String
Dim V_month As String
Dim V_year As String

V_day = CStr(day(Date))
V_month = CStr(month(Date)) - 1 '11 - 1 = nov. 00 is jan ??? Strange but true
V_year = CStr(year(Date))
stock = InputBox("Give stockname : " & vbCrLf & _
"(IFN, EWN, EWZ, EWW, APB)")

If stock = "" Then
Exit Sub
Else
stringstock = "hypertexttransferprotocol://ichart.finance.yahoo.com/table.csv?s=" & _
stock & "&d=" & V_month & "&e=" & V_day & "&f=" & V_year & "&g=d" & _
"&a=" & V_month & "&b=" & V_day - 1 & "&c=" & V_year & "&ignore=.csv"
On Error GoTo errorhandler
Workbooks.Open (stringstock)
'here the rest of the code to copy from a2:?2 to your workbook at the sheet
'with the data of the stockitem.
errorhandler:
'when you fetch the data at 1 am in the morning there is no data
'for that day. Therefore a msgbox to show why no data was found.
MsgBox ("There was a problem fetching new data" & vbCrLf & _
"for the stock : " & stock & vbCrLf & _
"on the data : " & V_day & "/" & V_month + 1 & "/" & V_year)
End If
End Sub[/vba]
Charlize

[Charlize]

Try this workbook. Stockquotes ewp and eww will work for the daily quotes. When there is data for the day it will be copied to the right sheet and the sheet table.csv is just closed after the copy. There are also some messageboxes to inform you what is happening. The fetching is based on the day of your computer. So to fetch some testdata, change your clock to 5 or 6 or 7 or 8 or 9 or 10 or 11 or 12 november 2006 or all those days when fetching data. Hope it will satisfy you to develop your project a little further.

Charlize

[cmagalha]

Helo, Chalize !
Many thanks !
I am stuck with something else, but I got some code from your previous post that I used with success.
The "model" workbook that I use for each stock (in fact they are investment funds) is more comkplex than what it looks. I use one workbook with 4 sheets for each ticker, with several pre-defined graphs on them.
I will post a comprehensive answer, with example, soon. In the meantime, thank you for the help !

[Charlize]

Your welcome. I believe that those daily stockquotes are finished as of 12 nov 2006 (Unless I have misread it on the site of yahoo ?).

Correction: They still provide stockquotes but the day after. If you change in code
[vba]Vday = Cstr(day(date))[/vba] with [vba]Vday = Cstr(day(date)) - 1[/vba] it will fetch the stockdate of the day before today.

- I think that when you change in the stockstring the item g=d in g=w or g=m you'll get either weekly or monthly data. Just a guess, not tested. If true, code must be different to fetch all the data that is fetched. The daily quotes were just one row to copy.

Charlize

[cmagalha]

Charlize
You're very helpful.
As I mentioned, I used some of your ideas and coded the following:
===============================================
Sub GetQuotes()
Dim dia As String ' Day
Dim mes As String ' Month
Dim ano As String ' Year
counter = 1
' "Do Until" bellow gets data from first 4 columns (ticker, day, month, year), line varying from 1 to end of data.
' this date is the "up to" date to get quotes. The starting date is always 01 Jan 1996.
Do Until Len(Cells(counter, 1)) = 0
ticker = Cells(counter, 1)
dia = Cells(counter, 2)
mes = Cells(counter, 3)
ano = Cells(counter, 4)
MsgBox ("Processing: " & ticker & " to date: " & dia & " . " & mes & " . " & ano) ' Remove this later
' Opens a workbook and load data into it.
' Questions are:
' How to open an existing workbook (MODEL.xls) and load data into a previusly defined sheet ?
' How to execute a VBA macro against the data just downloaded, to format it according to the needs ?

Workbooks.Open "hipertexttransferprotocolt://ichart.finance.yahoo.com/table.csv?s=" & ticker & "&d=" & mes & "&e=" & dia & "&f=" & ano & "&g=d&a=3&b=1&c=1996&ignore=.csv"

ChDir "C:\Mdocs\ADRsETFs\TESTES"
ActiveWorkbook.saveas Filename:=ticker & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Close
counter = counter + 1
Loop
End Sub
=====================================================

What I did was to "parametrize" the ending-dates, as this will vary every day. I do not care about intraday quotes. I only need quotes up to the previous day.

The starting date to collect data will always be in 1996. If it is not there, does not matter. Yahoo will provide what is there. And I would work with the last FOUR years of data, although I do care for having weekly and daily data for more than 4 years. Monthly data, however, should be limited to 48 months. If I collect more, I would have to re-work the MODEL.xls to provide more space for extra data.

At this point, the questions are:

1. How to open an existing workbook (MODEL.xls) and load data into a previusly defined sheet ?
2. How to execute a VBA macro against the data just downloaded, to format it according to the needs ?

And yes, I do have daily, weekly and monthly data to download. But, since the process is very fast, I do not care for executing the macro three times for each ticker, modifying the source URL accordingly.

My most important question at this time would be: how to open the MODEL.xls and use it as a base ?

Attached are:
1. BIF.xls (as example of a fully loaded workbook)
2. MODEL.xls ( the mask, with three sheets, where to load the data).

For some reason, I was not able to upload data. Maybe too big ? Anyhow, should you like to see it, please drop a note to carlosmagalha@yahoo.com and I will send it.

Thanks !

[stanl]

This has been a very useful thread. I had developed an application for middle-school/HS students to look into companies listed in the exchanges, but never thought of adding an additional tab to get recent activity. Stan

[Charlize]

[VBA]Workbooks.Open ("Model.xls")[/VBA] will open the workbook. Must reside in the same directory as the workbook who wants to open. If you're file is already saved before ... must include xls. I would include the patch in the save as - thing instead of changing the directory before saving.

Charlize

[Charlize]

Well, if there is data, excel will download it to a workbook called table.csv. This table becomes the active table (so just open the file that's starting everything and close every other file). I knew that it was a maximum of 1 line at row 2. So I checked against a value in A2. If there was a value, copy from a2 to g2 (because a manual dowload showed that data was filled from a to g).

First try a manual way and decide what needs to be copied. You have an additional problem with the no of lines. Use the code that I used to determine the line where to copy to.

Charlize

[Charlize]

[VBA]
'this line will give the lineno where to copy to
stockrows = Workbooks("Yahoo.xls").Worksheets(stock).Range("A" & Rows.Count).End(xlUp).Row + 1
'table.csv is active and selected.
Range("A2:G2").Copy Workbooks("Yahoo").Sheets(stock).Range("A" & stockrows)
[/VBA]
Charlize