Excel function Template to create yearly average of monthly stock averages for a Series of downlaoded stocks. ?Which service

Hi Jerome,

This is a nice one

there are two i do this
-one is daily updates takes only current day quote through http in a vb comp
-second what's more your solution i guess is import the data through a set of webqueries

if you can give a list of Ticker symbols needed for Yahoo then i'll build the workbook

:O)Bruintje

important note which version of excel is this?

Microsoft Excel 2002 / XP.
I have a long list  I use
AEE AEP AMN AOL ARBA AT AV AWE AZR BA BLS BP C CRA CVX DCLK DPH DSS DTE DUK DVN EAS EBAY EDS EK EMN EP ET ETR FBGRX FDX FFSL FMAGX FON FRESX GCO GE GILD GITAX GM GMH GP GPU GS GSF HGSI HLT IBM INKT INTC JNPR JPM KMG LOR LU MCIT MEL MIR MRO MSFT NAV NCR NEM NI NVS OXY P PCG PCL PCS PDLI PFE PPE PRMTX PRSCX Q QCOM QLTI QLTI RD RDRT RJR SBC SCG SO STOSY SUNW T TMX TX UAL UCL USG VGSIX VIAB VOD VRSN VZ WCOM WDC WFC WSF XOM YHOO

Also over the years some symbols may change or become inactive so ? traps in the code to add new symbols and discregard old symbols that no longer work.
I would probably do this at the end of the year dec 31st so have all the month to month averages and the average of these per stock.  Hope this makes sense
Many THnaks
Jerome Klein

Hi again,

before leaving, you can pickup a first version here

http://www.bredlum.com/ee_temp/yah_loader.xls

i'll write the documentation tomorrow

there are a few caveats with this approach as with any free approach but if i can come with a clear description you can cover most of them would they occur

:O)Bruintje

WOW, WOW, WOW..
THIS IS TRULY REMARKABLE,
A couple of notes
1. on citibank c, when i check yahoo i get the following
Feb 01 55.75 56.67 45.53 49.18 10,311,700 48.55
Jan 01 55.20 56.97 54.51 55.97 15,735,600 55.12
on your spreadsheet I get
1-Feb     55.75     56.67     45.53     49.18     10,311,700     48.55
1-Jan     51.73     57.35     50.07     55.97     13,246,900     55.12
I just used one stock to double check. Jan first two numbers are off for some reason

2. On the data sheet it reads as jan 1 2002 vs jan 2001 when you click on a cell
3. Also on the data sheet have to guess what stock it is.  Can you put the symbol so can manually double check a few to verify consistency.
4. Is it easy to change the year when next year comes along?

Jerome
 

PS to above note in awe.  Never saw a web query before
Citigroup is accurate, I must have entered the wrong day of the month to start with.
Jerome

Hi Jerome, yeah i know it's a neat feature, and i use it since the crammy version in XL97, and even then they already worked they got not as nice an interface to choose the tabel you want to import from a page but you've to do some manual guessing now also...

i'll write a little manual later today and post it here with some code you can find in the workbook

btw not many people know it exist or got a immediate application for it and forget about it, for me it's the fastest way of a non-code solution importing data from the web if you want you can get complete webpages into a sheet but it would become very slow so for data and especially numbers formatted in tables it's ideal

Brian

Brian:

Will you be able to modify for the 3 issues below

1 On the data sheet it reads as jan 1 2002 vs jan 2001 when you click on a cell
2. Also on the data sheet have to guess what stock it is.  Can you put the symbol so can manually double
check a few to verify consistency.
3. Is it easy to change the year when next year comes along?

Can I give you an A+++. Can I increase the points?
Do I do it now or will it close out the screen for you final posting??
Thanks
Jerome

Hi Jerome,

writing documentation isn't my hobby ;)

but here we go also for the PAQ readers and the betterment of EE :O)))))

-first open a new workbook
-add 3 clean sheets
-first one = SymbolList
-second one = Data
-third one = Worksheet
-then open the VB Editor with ALT+F11
-insert a new module

and paste this complete listing of the 3 utilities needed for further processing

Option Explicit

'important procedure to build up the raw data sheet
'with the symbollist sheet it gets all data from yahoo
Public Sub GetAllStocks()
'declaration of used variables
Dim i As Integer
Dim strURL
Dim iSel As Integer

'this counter is for the cell where the
'web query put it's output
iSel = 1
'in sheet data select A1
Worksheets("Data").Range("A1").Select
Application.ScreenUpdating = False
'loop through the list of symbols this is hard
'coded could be made variable or just replace
'the 107 with the last used row number
For i = 4 To 107
Debug.Print Now
    'get the url where the data most come from
    strURL = Worksheets("SymbolList").Range("C" & i)
    'now add a new web query component to the active sheet
    'in this case it will be the data sheet
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & strURL _
        , Destination:=Range("A" & ActiveCell.Row))
        .Name = "YEARLY STOCK LIST"
        .FieldNames = True
        .RowNumbers = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertEntireRows
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
'********this line is important it gets the
'********table number 6 from the html page
        .WebTables = "6"
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = False
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    'now take enough rows to fill out the table
    'in our case the table is around 18 rows so take
    '20 rows in our excel sheet to place the next web
    'query component on the sheet
    iSel = iSel + 20
    Range("A" & iSel).Select
Next
Debug.Print Now
Application.ScreenUpdating = True
End Sub

'the calculation of the averages
'use the worksheet function to do
'this fastest and easiest
Public Sub CalculateAverage()
'declare variables
Dim i As Integer
Dim j As Integer
  'select your worksheet
  Worksheets("Worksheet").Select
  i = 0
  j = 3
  'again hard coded the list start with 4th row and loop to row 107
  For i = 4 To 107
    'go to the data sheet to check if there's any data for this stock
    If (Worksheets("Data").Range("G" & j) <> "" Or Worksheets("Data").Range("G" & j + 1) <> "") Then
      'if there's data fill the average for this stock
      Range("C" & i) = WorksheetFunction.Average(Worksheets("Data").Range("G" & j & ":G" & j + 17))
    Else
      'else set it to null there's no data probably wrong symbol
      Range("C" & i) = 0
    End If
    'jump 20 rows on the data sheet to get to next stock data
    j = j + 20
  Next i
End Sub

'small procedure for adding the symbols to the data sheet
Public Sub AddSymbol2Data()
'declare variables
Dim i As Integer
Dim j As Integer
  'select the data sheet
  Worksheets("Data").Select
  i = 0
  j = 1
  'again our hard coded loop through the symbollist
  For i = 4 To 107
    'set the cell in column M to the symbol found in the list
    'give it a color and some formatting for easy reading :)
    With Range("M" & j)
      .Value = Worksheets("SymbolList").Range("A" & i).Value
      .Interior.ColorIndex = 40
      .Font.Bold = True
      .Font.Underline = xlUnderlineStyleSingle
    End With
    'again next 20 rows for the next symbol
    j = j + 20
  Next i
End Sub

-save this and close the VB Editor
-now we've got the code

-second we're going to build up the workbook part
-first on the SymbolList we add all symbols from row 4 down to whatever we need (but if you add more then 103 symbols you got to change the code as is documented in the code itself see comments at row 107 in the 3 procedures)
-do this starting in A4 and all the way down
-now in cell D1 we enter this formula for the start year

=RIGHT(YEAR(TODAY())-1,2)

-this one will always ensure this year-1 in that cell so if it's 2003 there will be 02
-now in cell G1 we enter this formula for the end year

=RIGHT(YEAR(TODAY()),2)

-this one will always ensure this year in that cell so if it's 2003 there will be 03

-now in cell C4 enter this formula for the URL string we need in the web queries

=IF(A4<>"","http://table.finance.yahoo.com/d?a=1&b=01&c="&$D$1&"&d=1&e=01&f="&$G$1&"&g=m&s="&A4,"")

-it checks if a symbol is in A4 and if it is will build the URL string for you using the years in D1 and G1
-now copy this C4 formula all the way down for as many rows you got symbols in the A column

-congratulations we're almost there
-on the third sheet called Worksheet we enter a link in Cell A4 to Cell A4 on the SymbolList sheet like

=SymbolList!A4

-and copy this down for as many rows we got symbols in the list

-now comes the hard part :)
-first choose tools | macros | run | GetAllStocks
-this will take for the 103 symbols we started with some 6 minutes(ADSL) so take a break from your hard work and let the thing work for you
-it will even report when it's done with a small box saying "Finished getting data"
-click OK
-now again choose tools | macros | run | CalculateAverage
-this will put all yearly average on the worksheet behind the symbol
-it will report also with a box saying "Finished calculation"
-click OK
-finalizing for double-checking we have to add the symbol to the second sheet called Data
-now again choose tools | macros | run | AddSymbol2Data
-this will add the symbol next to the data table with a color and format
-it will report also with a box saying "Finished processing"
-click OK

-and we're done
-now you can cross check the data

Wow, i never thought of writing tech articles, but it almost resembles one

We even got our official FAQ

Q.
On the data sheet it reads as Jan 1 2002 vs. Jan 2001 when you click on a cell
A.
i know it's in the format i got out of the html table difficult to change, what i did and what worked for most of the data is
-select column A
-then choose format | cells | special | enter mmm yyyy
-apply this worked for most of them
-the column is part of the web query
-only solution is to copy the data to another sheet and paste special only values + formats and try to get it better that way

Q.
Also on the data sheet have to guess what stock it is.  Can you put the symbol so can manually double
A.
this is taken care of
check a few to verify consistency.

Q.
Is it easy to change the year when next year comes along?
A.
Well that's all automatic

when i saw this Q i already knew it should be fun although Im a fervent supporter of GOLD + SILVER, i like everything financial industry related

well i wish you could give me a AAA or triple i would like that, about the points yeah you can up them but it isn't necessary i got so many still to go before i get anywhere besides this is all for fun and to help others out

Brian

PS you can pick up a new version with the changes in it
http://www.bredlum.com/ee_temp/yah_loader.xls

AAAAAA+
Many thanks
2 questions when I start tne new version it doesn't automatically query the internet just kind of stays put, is this correct old version everytime I opened it redid the numbers

Also the dates show as 2002 vs 2001 which make it unsettling ie put pointer on cell 1-Dec shows dec 1 2002


Date     Open     High     Low     Close     Volume     Adj.    
                              Close*    
1-Dec     41.4     43.98     40.71     43.53     1,313,800     42.9    
1-Nov     $0.60 Cash Dividend                              
1-Nov     42.21     44.84     39.71     41.25     1,212,000     40.65    

THanks Jerome

Many THanks
I should have read in detail your whole explanation of the template.  But I was going to wait till tonight when I was relaxed and could absorb this.  Felt I was back in calculus which I loved but have to pay a lot of attention to the detail.
Many Many thanks
Jerome

AAAAA+++++ Triple Wow, light years ahead of most programmers.  Really really impressive.  Thanks
Jerome

that's too much honor, but thanks for the compliment

and thanks for the points

Brian:
I switched the year to 2000 to 2001 to see if it would make a difference.  It came up with the same figures as 2000 to 2001.
?code problem
Jerome

Correction I should have said results did not change from year 2001 to year 2000.
?code issue
Jerry

Correction I should have said results did not change from year 2001 to year 2000. I changed my computer clock to say April 28 2001.
?code issue
Also when I read the your directions I don't have to run the macros each time as they are run automatically?? If I try to run them separately get error messages
Jerry

Hi Jerry,

This thread is still active, only id din't receive the coment notifs that's strange but to answer your questions

1.
two solutions
-turn on automatic calculation in tools | calculation | automatic (if this still doens't work, use F9 that will recalculate the dates)

2.
about the running the macros
-they are in turn of each other exactly as described above

for example
-first do a recalculate of the dates
-then on the <data> sheet do a select all (CTRL+A)
-hit the delete button (say yes to the box)
-then run GetAllStocks
-then run CalculateAverage
-then run AddSymbol2Data
-then run the last macro i added and forgot to paste here

i'm here to help you as long as needed to get this running as you wish

Brian

Brian:
Thanks, but I am confused.
1. If I manually do the above it works. Yet when i open the file each time it seems to run some kind of internet query but doesn't do what's necessary?? Not certain what it is doing at this point.
Is there a way to have all the macros run above automatically on opening the file or have a message box which gives you a choice of automatically running them on opening or have it do what it normally does on opening.
Also it addes a few extra dates at the end, no problem just wanted you to know ie Jan 1900
Aug 2000     36.36     40.61     36.31     40.45     548,500     36.42    
Jul 2000     34.14     36.92     34.14     36.19     338,900     32.58    
Jun 2000     36.47     36.81     33.36     33.75     293,700     30.38    
May 2000     36.44     37.59     34.84     36.69     378,500     33.03    
Apr 2000     30.9     37.99     30.67     36.69     496,600     33.03    
Mar 2000     29.78     31.14     27.59     30.94     317,700     27.85    
Feb 2000     32.79     33.39     28.54     30     185,200     27.01    
Jan 2000     32.54     34.19     31.64     32.56     700,800     29.31    
Jan 1900                                  
Jan 1900                                  


Date     Open     High     Low     Close     Volume     Adj.    
                              Close*    
Dec 2000     45.68     48.88     43.11     46.5     1,646,300     43.46    
Nov 2000     $0.60 Cash Dividend                              

I hope this makes sense.
Thanks again
Jerry

Hi Jerry,

lost my machine for a while to answer your questions

-the opening of the workbook i'll change that and post a new link here tonight

-the dates i know, i was to lazy to have that handled right but will take care of it also in a new template

in 4 hours a new version will be available ;)

Brian

Brian
Many thanks.  Sorry to be a pain. Awful when the computers crash and loose them.

My computer is coming on 2 years of constant use.  I am waiting till the p4-s come down in price and they get the kinks out to buy one.  
Jerry

Hi Jerry, sorry for the wait, it was my PC at the job so it wasn't that bad except for no contact for half a day :)

the pc's will become interesting again, when Intel gets the 64 bit going for real, and the asians will drop their rimms in price so you can buy a 40GB of static ram instead of a 40GB on a harddisk

had a long talk today about the time where you switch on the pc like a tv/radio and it gives an instant reaction on what you want, reading mail, watching news etc.....5 years maybe? or 10 years....of course then prices have to come down......

a new template is stored here

http://www.bredlum.com/ee_temp/yah_loader.xls

i put in a update button, so on open it shows an empty data sheet, and the queries will not update on open, you got to use the button to do that

your not a pain, really i want the solution to be complete and it is not complete untill you say so

Brian

Brian
Many, many thanks.  Works fine.
2 final questions.
If and when more symbols change the one line of code or I need to change more than one line that references.
When I load the xls, it does a web query series of checks, in reality not doing anything
Again many many thanks
just perfect
Jerry