Get the closing stock price for the first and last day of the year

[Mustang65]

I have looked around and could not locate the formula I need for my automated stock program. I know it is possible.. I am not sure as to how I can go about it...

The Yahoo Stock Quote History data has the:
"DATE" (01/01/16) in column "A",
"Opening" price is in column "B",
"Closing" price is in column "E".

I also have a YEAR in Column "H" using the formula =IF(B2="","",YEAR($A2))

Since the first trading day of the year is not on January 1, I need to find the "Opening" price of the first trading day of a specified year and the "Closing" price of the last trading day of that year. The year that is used for the search is located in the fields below. The Yahoo Stock Quote History downloaded data is for (current date data is located in A2):
Current year = Year to Date (2016) - Year located in AF40 as 2016
Current year - 1 (2015) - Year located in AF39 as 2015
Current year - 2 (2014) - Year located in AF38 as 2014
Current year - 3 (2013) - Year located in AF37 as 2013
Current year - 4 (2012) - Year located in AF36 as 2012
Current year - 5 (2011) - Year located in AF35 as 2011

The output would fill:
2016 (AG40), Open Price (AH40), Close Price (AI40)
2015 (AG39), Open Price (AH39), Close Price (AI39)
..
..
..
2011 (AG35, Open Price (AH35), Close Price (AI35)

I was thinking of finding the last row with data in column "A", stepping through each row from the bottom up and when the year matched use that row as the first trading day, get the data, save the data in the designated cells and once that was achieved, continue stepping upward until the next year matched, back up a row and get the closing price from the previous year... restart stepping upward and continue until the 1st record is reached (most current stock price information) ???

The other way I was thinking of doing it was to use the "YEAR" in Column "H" and step upward using that field with pretty much the same logic as above???

Any thoughts... EASIER way?
Don

 

[igold]

Hey Don,

The Yahoo API is smart enough to know when the first trade date of the year is. This is a portion of code I use for historical quotes from Yahoo. If I use 1/1/16 as the start date, Yahoo will return the automatically return 1/4/2016 as the first trading date of the year. There is no need for me to know what the first trade date of the year is, Yahoo does.

SYMBOL = QuoteFrm.SymbolLbl
    StartDate = QuoteFrm.tbStartDate2.Value
    EndDate = QuoteFrm.tbEndDate
    w.Activate
    Range("B1").Value = SYMBOL
    Range("D1").Value = StartDate
    Range("D2").Value = EndDate
    Range("A4:G10000").ClearContents
    
    url = "http://ichart.finance.yahoo.com/table.csv?s=" & SYMBOL
    url = url & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=d&q=q&y=0&z=" & _
            SYMBOL & "&x=.csv"

As I said this is just a portion of the code I use, it draws the variables from the userform "QuoteFrm", but if you can follow it and decipher the url then you will see what I mean.

It is Friday night and I have had a couple so I am not sure if this will help you, but I will restate. Given the url in this code, if I enter 1/1/2016 as my start date, Yahoo will automatically return 1/4/2016 as the first trade date of the year...

HTH

igold

 

[Mustang65]

Thanks igold,
But I currently download all the data "Current year" to "Current year -5" in one dump and I utilize all the data for the various formulas. This portion of the VBA code is to give me a summary by year on the results of if I purchased the stock on the first trading day of the year and sold it on the last trading day of the year and to compare to my program logic to see which would have produced the more profitable return for the year(s). I have the code working for a single stock quote at a time, and I am still working on the part where the program gets the Symbol from a worksheet list and downloads the data, does its calculations, and copies the results to a summary report for all the stock symbols, by Current Year, year-1, year-2, year-3, year-4, year-5, P/L on 1st day - Last day purchase. This eliminates stocks that do not perform well using my formulas.. narrows down the list of possible candidates.

So I need to continue working on the VBA to acquire the 1st/Last trading day numbers. I will keep the code for future reference, thanks

Don

 

[Mustang65]

igold

Sorry about that.. I just looked over the code and I believe it will work just fine.. will let you know how it turns out. Should save a lot of code
Thanks
Don

 

[igold]

If you need additional help let me know. I pull a ton of info many times daily from the Yahoo Finance API...

Just curious... where are you in the Sunshine State...

igold

 

[Mustang65]

I live in Dunedin FL, the next city north of Clearwater, about 200 yards from the gulf. Enjoying LIFE!! I hope to sit down tomorrow and give the code a try.

Thanks again,

Don