PDA

View Full Version : Solved: Interfacing VBA-Excel and Yahoo finance



cmagalha
11-03-2006, 06:40 AM
My knowledge of VBA is minimal. :help 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:

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

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
11-04-2006, 12:30 AM
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
11-04-2006, 12:35 AM
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
11-04-2006, 05:15 AM
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
11-04-2006, 06:03 PM
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
11-05-2006, 11:22 AM
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
11-08-2006, 06:47 AM
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
11-09-2006, 04:22 PM
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/table.csv?s=EWP&a=03&b=1&c=1996&d=10&e=5&f=2006&g=d&ignore=.csv

Charlize

cmagalha
11-09-2006, 04:33 PM
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
11-09-2006, 04:43 PM
try this one :
Sub tickertwo()
Workbooks.Open ("http://ichart.finance.yahoo.com/table.csv?s=EWP&a=03&b=1&c=1996&d=10&e=5&f=2006&g=d&ignore=.csv")
End Sub

Is it this what you're after ?

Charlize

cmagalha
11-10-2006, 02:02 PM
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. :wot 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
11-10-2006, 05:26 PM
And what about this one. Uses the date of the computer to fetch stockdetails. Hope this will help a little.
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
Charlize

Charlize
11-12-2006, 06:39 AM
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
11-13-2006, 08:31 AM
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
11-13-2006, 04:07 PM
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
Vday = Cstr(day(date)) with Vday = Cstr(day(date)) - 1 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
11-14-2006, 06:46 AM
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 (http://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
11-14-2006, 07:51 AM
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
11-14-2006, 07:56 AM
Workbooks.Open ("Model.xls") 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
11-14-2006, 08:01 AM
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
11-14-2006, 08:06 AM
'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)

Charlize

cmagalha
11-14-2006, 08:33 AM
Charlize
Very helpful. I will look in detail to see how can I code this into my workbook - as I mentioned, I am very limited in VBA.
Will reply later.

cmagalha
11-14-2006, 08:46 AM
Stanl

Vary interesting approach for HS. In my country they do not do this, ever. I mean: touching real-life finances for HS.
I do look at the same company using a different approach - this is why I am trying to download data in an ordered way from Yahoo.
I got data for KRC and created my graphs based on them. Take a look to see whether this would be of interest.
Since we all know that stocks a a very long investment, I look at 48 month performance and also 36, 24 and 12 months. Furthermore, there are other graphs that you migth find interesting to look at. Specialy the "running averages".
Can't attach, since the ZIP file is about 709KB. Should you like, drop me a note and I will send it.

cmagalha
11-15-2006, 11:15 AM
My apologies.
I thought that I've responded but it looks like my post was lost.
I will work on the ideas presented starting today. Will post results in a day or two.

STANL: Interesting work of yours. In my country they do not teach financial backgroud to kids ! This is a must, of course.
I look the companies from a different perpective: I look to the past performance, to be able to estimate their future behavior. This is called "trend following". Of course it all depends on the market fluctuations, etc, etc.
For the sake of example, I downloaded data from KRC and created all graphs showing the past performance. It does have an impressive 295% accumulated return in 48 months, 167% in 36 months, 94% in 24 months and 22% up to date in 2996. Std deviation for negative daily returns of 3.23; std dev. for all daily returns 3.74. Avg month return of 2.85%. 80% of the monthly returns are positive.
I translated the titles of the graphs to ease understanding.
The workbook for KRC and also the MODEL.xls workbook can both be found at: http://www.4shared.com/dir/1290127/57227f5b/sharing.html
Files are zipped.

CHARLIZE: I will post a reply soon. Many thanks !

cmagalha
11-15-2006, 03:34 PM
I thought I had it right, but not ...

I am trying to accomplish data transfer from the TEMP workbook to the MODEL workbiik, sheet "diario", as defiined. Obviously I am doing something wrong here.

Yahoo responds with data, I see the little window showing data transfer, but the statement in red stops execution. The statement in blue never gets executed.

Any ideas ?
If this works, I think I can replicate for monthly and weekly data. After that, all I would need would be execute some formatting macros.
Thanks again.

================================================
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
' This URL gets daily data from Yahoo
Workbooks.Open "hipertexttransforprotocol://ichart.finance.yahoo.com/table.csv?s (http://ichart.finance.yahoo.com/table.csv?s)=" & ticker & "&d=" & mes & "&e=" & dia & "&f=" & ano & "&g=d&a=3&b=1&c=1996&ignore=.csv"

dailydata = Workbooks("model.xls").Worksheets(diario).Range("A2:G5000")

' should copy data from temp worksheet A2:G5000 to "model", sheet "diario", starting from A2 up to G5000

Range("A2:G5000").Copy dailydata

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
===============================================

Charlize
11-15-2006, 04:38 PM
'table.csv is active and selected.
Range("A2:G5000").Copy Workbooks("model.xls").Sheets("diario").Range("A2:G5000")Charlize

cmagalha
11-16-2006, 03:48 AM
I corrected my statements as suggested, , but Excel says:
subscript out of range.

The other wierd thing is that tha resulting data is NOT parsed anymore. It used to come out correctly in eaah column, but not now anymore. Is this "normal" ?

Charlize
11-16-2006, 06:06 AM
Yahoo and model must be open and in same directory. Remember to name you ticker sheets in the vba editor. The name there is different from the name on the tab. Also the tickers will be saved. When already saved they will be overwritten without a notice.

Charlize

Charlize
11-16-2006, 06:08 AM
You just can't say a2:G5000 if there's nothing to copy from. When you copy to, you must also clear the area before the copying takes place (.clearcontents).

Charlize

mdmackillop
11-16-2006, 02:00 PM
Hi Charlize,
Great job here. I look forward to a nice KB item on the subject.
Regards
MD

Charlize
11-16-2006, 04:51 PM
A tweak here and there to make sure everything works as planned.
1. check if model.xls is open else macro won't run.
2. check if beginning workbook is yahoo - cmagalha else there will be errors.
3. check on model.xls if more then one row because I like the headings.
4. check if there is data to copy from temp table.csv else we are losing time.
5. notification on what stockitem he is busy ...
6. maybe you can add a msgbox at the end to say everything is fine and not to forget to save model.xls

- everything must be in the same directory.

Hope you will like it.

Charlize

Charlize
11-17-2006, 04:21 PM
Seems to be a problem with the saving of the seperate tickers in excel xp. In excel 2003 it works. Does somebody have a suggestion ?

Charlize

cmagalha
11-18-2006, 03:16 AM
Charlize, many thanks !

I will study your code carefuly.

But, before I do that, let me show what I've got working so far.
I was able to download data, format it in some way and create several workbooks, one for each ticker (74, I guess). I figure that I could define a folder for each type of data (daily, weekly or monthly) and have, at least, some data to work with. :doh:
However, it is not that easy. I do need to perform some calculations on the data downloaded. And, since the workbook that gets created is an empty one, no formulas are there. :(

So , before I look at your code, may I ask one question: how do I add a formula to a newly created workbook, before saving it ?

This is the problem: I have data on column G. On column H I need to have the percentage variation between two adjacent lines on column G to determine daily, weekly or monthly variations.

An example is best:
Cells G2 and G3 have data. On cell H3, I need: =((G3-G2)/G2)*100
And so forth. How to insert the formula on H3 and expand it for all lines up to G1000 (or end of data on column G) ?

For the record, this is the code I have working so far:
==================================================
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
Workbooks.Open "hipertexttransferprotocol://ichart.finance.yahoo.com/table.csv?s (http://ichart.finance.yahoo.com/table.csv?s)=" & ticker & "&a=3&b=1&c=1996" & "&d=" & mes & "&e=" & dia & "&f=" & ano & "&g=w&ignore=.csv"
Range("A1:A3000").Select
Selection.TextToColumns Destination:=Range("A1"), 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("A1:A3000").Select
With Selection
.HorizontalAlignment = xlCenter
End With

Range("A1:G3000").Select
With Selection
.HorizontalAlignment = xlGeneral
.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

Range("G2:G3000").Select
Selection.NumberFormat = "0.00"

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

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

mdmackillop
11-18-2006, 04:04 AM
On cell H3, I need: =((G3-G2)/G2)*100
And so forth. How to insert the formula on H3 and expand it for all lines up to G1000 (or end of data on column G) ?



Range(Cells(3, 8), Cells(Rows.Count, 7).End(xlUp).Offset(, 1)).FormulaR1C1 _
= "=((RC[-1]-R[-1]C[-1])/R[-1]C[-1])*100"

stanl
11-18-2006, 09:06 AM
I look the companies from a different perpective: I look to the past performance, to be able to estimate their future behavior. This is called "trend following". Of course it all depends on the market fluctuations, etc, etc.


You might want to test your analysis against another site
ticker = "MSFT"
cURL= "http://moneycentral.msn.com/investor/research/profile.asp?Symbol= (http://moneycentral.msn.com/investor/research/profile.asp?Symbol) " & ticker

I use a simple web-query Stan

cmagalha
11-18-2006, 10:56 AM
Yes, I m aware of MSFT and the moneycentral website.
But I'd rather use Yahoo Finance as it seems easier to get historical quotes. And I am more focused on ETFs (Exchange Traded Funds) for analysis and investing.
Thank you.

cmagalha
11-18-2006, 11:06 AM
Thank you again.
I tested the code, but was not able to make it work. I needed to make some minor changes, but it did not work before them.
I am copying here, to be able to coment on top of the code.

Data is recovered, a new workbook is opened, showing data. Note that the model.xls is left untouched.
A "subscript is out of range" message is presented on an error message box, pointing to the line in blue.

"model.xls" is my model, a 2.22MB workbook, with 4 sheets: DADOS, QUINZ, DIARIO and GRAF. This last one has all pre-defined graphs prepared. DADOS would hold monthly data; QUINZ weekly data; DIARIO daily data. It was open before I started the process.

======================================================
'---------------------------------------------------------------------------------------
' Module : Module1
' DateTime : 17/11/2006 00:41
' 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 ("hipertexttransferprotocol://ichart.finance.yahoo.com/table.csv?s (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")

' m gets monthly data

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

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

' 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.
stockrows = Workbooks("table.csv").Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
If stockrows <> 1 Then
' changed from "A2" to "A4"
Range("A4:G" & stockrows).Copy Workbooks("model.xls").Sheets("DADOS").Range("A4:G" & stockrows)
End If
'ChDir "C:\Mdocs\ADRsETFs\mensal"
'ChDir ("C:\DATA")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=ticker & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close
counter = counter + 1
Unload TickerProgress
Loop
End Sub
===================================================

Any ideas ? Did I do everything correct ?

Charlize
11-18-2006, 01:16 PM
I made a sheet DADOS and the no of stockrows were counted. But you have to make a monthly sheet for every ticker in your model workbook. You call them EW - D, EWW - D, ... so you can save to ticker & " - D". Because when you've got 79 tickers only the last one will be saved to your monthly sheet.
Why the change from 2 to 4 ? So you wont copy the 2 datalines of the table.csv ?

Charlize

cmagalha
11-20-2006, 02:35 AM
Changing from 2 to 4 was a mistake, caused by my lack of knowledge of VBA. The data starts on A2, but the destination is really on A4, because of the headings.
But ... after a little tweaking, it is now working, partialy. Details will follow.

cmagalha
11-20-2006, 03:13 AM
Partially, but it works, Charlize !:bow:
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 ! :clap:
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
:mkay
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 (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
Application.DisplayAlerts = True
ActiveWorkbook.Close
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
11-20-2006, 03:42 AM
Look at the order of your workbooks.
1. Model open
2. Yahoo was open but then we activate
3. Model activate
4. Save it with the name of the ticker
5. close model

This means that table.csv is still open so no new data can be fetched.

Also look at the number of stockrows. If you say 50 that means 49 because you start from A2 to A50 (for table.csv). For the model when starting at 4, you must say 54 (stockrows + 4) else there isn't enough place to copy to.

Charlize

cmagalha
11-21-2006, 05:57 AM
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
11-21-2006, 04:22 PM
Partially, but it works, Charlize !:bow:
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 ! :clap:
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
:mkay
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 (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
:exclaim: workbooks("table.csv").activate
:exclaim: activeworkbook.close
Application.DisplayAlerts = True
'ActiveWorkbook.Close
:exclaim: 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
11-23-2006, 06:35 AM
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
12-18-2006, 03:10 AM
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 (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
03-15-2007, 11:13 PM
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 (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
03-16-2007, 06:16 AM
I had problems with that site definition and hosting limitations.Please state your needs regarding the project results and I will respond.

RV6555
08-31-2007, 02:05 AM
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
08-31-2007, 02:15 AM
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.