Solved: Interfacing VBA-Excel and Yahoo finance

[cmagalha]

Charlize
The number of stockrows is Ok, because I will work - in the case of monthly returns - with 48 months only. So, from A2 to A50 I am getting all that I need. This part works fine and the data is placed where it should be, formatted, calculations are done and graphs crated fine.

However, I cannot find my way when it comes to the "open/close" sequencing. I downloaded a couple of VBA-Excel text-books and I looking atthem, for no practical result so far.

I did several atempts to make this work, but it still stops at the point I mentioned before. No LOOP !

I will keep looking. In the meantime, should someone wants to give this a shot, please, do so !

[Charlize]

Partially, but it works, Charlize !
Fact is that the code does create a workbook, with the Sheet(DADOS) populated with pertinent monthly data, formatted as needed and all calculations done. Graphics included. Fantastic !
I had to do some minor changes, commented.

However, is does NOT loop, to get the second ticker from the list. It stops with a message:
Execution time error 13; Incompatible types

The statement is:

stockrows = Workbooks("model.xls").Sheets("DADOS").Range("A" & Rows.Count).End(xlUp).Row

But the first workbook is perfectly saved having filename as the ticker name. Here it goes:
======================================================
'---------------------------------------------------------------------------------------
' Module : Module1
' DateTime : 17/11/2006 00:41; 19/11/2006: little modifications by Carlos
' Author : Charlize
' Purpose : Fetch stockquotes + save to model + every stockname a workbook.
'---------------------------------------------------------------------------------------
Option Explicit
' This workbook is also in the same directory. Everything in the same directory to test.
' And all files, model.xls and yahoo ... must be open. If model.xls isn't open there
' will be a warning to open it.
' When it does what it is supposed to do, implement the directory changes
Public dia As String 'day
Public mes As String 'month
Public ano As String 'year
Public ticker As String 'ticker name in column A
Sub GetQuotes()
Dim counter As Long 'the rowno beginning with 1
Dim stockrows As Long 'how many lines to copy

'Function call to determine if model.xls is open or not.
If IsOpen("model.xls") = False Then
MsgBox ("Model.xls is not open" & vbCrLf & _
"Open it before you run this code.")
Exit Sub
End If

'Be sure that this workbook starts as the active one.
Workbooks("Yahoo - cmagalha.xls").Activate
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).Value
dia = Cells(counter, 2).Value
mes = Cells(counter, 3).Value
ano = Cells(counter, 4).Value

' define the text to show on the form.
' This URL gets daily data from Yahoo
' Show the progress of the fetching in the caption of a form
' because I couldn't get a label updated
' We show it modeless because the code must continue to run

TickerProgress.Show (0)

Workbooks.Open ("http://ichart.finance.yahoo.com/table.csv?s=" & ticker & "&d=" & mes - 1 & "&e=" & dia & "&f=" & ano & "&g=m&a=0&b=1&c=1996&ignore=.csv")

' Sheets are: DADOS; QUINZ; DIARIO. In this case, monthly data, we will use "DADOS".

'Execution time error 13; Incompatible types, ON STATEMENT BELOW

stockrows = Workbooks("model.xls").Sheets("DADOS").Range("A" & Rows.Count).End(xlUp).Row

'This statement was causing it to stop, so I commented it out.

' if stockrows = 1 do not clear anything because I like my headings
'If stockrows <> 1 Then
' Workbooks("model.xls").Sheets(ticker).Range("A2:G" & stockrows).ClearContents
'End If

' If no data is found don't bother to copy something.
' FIXED NUMBER OF ROWS TO COPY: 50

stockrows = 50 'Workbooks("table.csv").Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

If stockrows <> 1 Then

' changed from "A2" to "A4" ON DESTINATION
' Range("A2:G" & stockrows).Copy Workbooks("model.xls").Sheets("DADOS").Range("A4:G" & stockrows)
' REMOVED TRAILING INFO ON DESTINATION RANGE

Range("A2:G" & stockrows).Copy Workbooks("model.xls").Sheets("DADOS").Range("A4")
End If
'========================================================================== =============
' Make the "model", with new data, as active
' Format data
'========================================================================== =============
Workbooks("model.xls").Activate

Range("A4:A52").Select
Selection.TextToColumns Destination:=Range("A4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1))
Range("A4:G52").Select
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("A4:G52").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Normal"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 11
End With

'========================================================================== =============
'ChDir "C:\Mdocs\ADRsETFs\mensal"
'ChDir ("C:\DATA")

Application.DisplayAlerts = False

' Make the "model", with new data, as active (MOVED ABOVE)
' Workbooks("model.xls").Activate ==> already done above

ActiveWorkbook.SaveAs FileName:=ticker & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
workbooks("table.csv").activate
activeworkbook.close
Application.DisplayAlerts = True
'ActiveWorkbook.Close
What are you going to do with model. must it be cleared for new data ?
counter = counter + 1
Unload TickerProgress
Loop
End Sub

Function IsOpen(FileName As String) As Boolean
' Determine if a workbook is open or not
Dim wb As Workbook
For Each wb In Application.Workbooks
If UCase(wb.Name) = UCase(FileName) Then
IsOpen = True
Exit Function
End If
Next wb
IsOpen = False
End Function
========================================================

Charlize

[cmagalha]

It tries to save de "ticker".xls, but no data was loaded into it. I think I made some mistake in handling the code; I will have to review it.

But, after I see the "Saving xxx.xls" on the Status bar, it also stops execution with message:

Execution time error: '9': subscript out of range.

At the statemente marked in red.
=======================================
.......

ActiveWorkbook.SaveAs FileName:=ticker & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks("table.csv").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
......

=====================================

Regarding future use for "model.xls": yes, it will be loaded with additional data on sheets QUINZ and DIARIO, and saved as "ticker.xls".

After that the application should clear (or realod it) and LOOP, repeating the process for the next ticker on the list.

[cmagalha]

Hi, guys !

This is to let everybody know that the Excel-VBA application is now working.

Thanks to 100west, mdmackillop, cyberdude, stanl and specially, Charlize.

Charlize helped me offline with lots of coding ideas. After letting the code sleep for two weeks, a couple of days ago, I came back to it and was lucky enough to make it work.

I am now able to interact with Yahoo Finance, get data (quotes), format it and create several graphs that help me in understanding the performance of a particular fund ou stock.

The results of a parallel work (not derived from this part directly) are being published to http://webetfs.atspace.com , for those interested in this investment area. It is a free website.

Anyone interested in this area can drop me a note and I will gladly discuss the subject.

Cheers !

Merry Christmas to you all !

[xls]

Hi, guys !

This is to let everybody know that the Excel-VBA application is now working.

Thanks to 100west, mdmackillop, cyberdude, stanl and specially, Charlize.

Charlize helped me offline with lots of coding ideas. After letting the code sleep for two weeks, a couple of days ago, I came back to it and was lucky enough to make it work.

I am now able to interact with Yahoo Finance, get data (quotes), format it and create several graphs that help me in understanding the performance of a particular fund ou stock.

The results of a parallel work (not derived from this part directly) are being published to http://webetfs.atspace.com , for those interested in this investment area. It is a free website.

Anyone interested in this area can drop me a note and I will gladly discuss the subject.

Cheers !

Merry Christmas to you all !

This site is not opening is that email id is correct & can u post here final sheet or link to that final sheet uploaded elsewhere!

[cmagalha]

I had problems with that site definition and hosting limitations.Please state your needs regarding the project results and I will respond.

[RV6555]

Hi, the question of previous post was whether you could post your final file. This would enable other ppl to use it too, I would be very interested

Thanks,

Roy

[Charlize]

I've resubmitted this idea to become a kb-article after some tweaks because of the saving problems with table.csv . Sunday I will be back at home, so I could post this changed file (but it's without the graphs, only numbers) if it's not already an article.