yahoo finance

By , October 31, 2017 2:10 PM

How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too)

How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too)


 

Introduction

I was working on creating a spreadsheet to calculate profits and losses on options positions but didn’t know how to populate excel with stock quotes. Back in the day there used to be an interface to get stock quotes with the MSN Money site but it is not supported anymore. The idea behind this spreadsheet was to use the latest and historic quotes to calculate intrinsic values of options and P&L for expired ones. Kind of just trying to keep track of my record and evaluate performance. Of course the issue we face is that stock prices move every second and maintain all that data manually is not worth it. After some research I tried using Google Finance to populate Excel to no avail but found Yahoo Finance supports this more easily. I ended up writing a post to help others with regards to that: How to: Obtain stock quotes from Yahoo finance (you can query them via Excel too).

Just recently, a reader asked about making the query date specific. Yahoo! Finance does support getting historical (closing) prices. It is a very basic interface, so it comes with a lot of limitations. If you just want historic closing prices, then this is the place for you. Within the limitations I have found (obviously there are more but from my simple needs), I have identified these:

  • You are NOT able to get more than one Stock or Index at a time
  • You are NOT able to download data for everything (exchange rates is one example, there are some “weird/foreign” stocks as well)

but now that I have completely taken all the enthusiasm away from you, let’s get into the exciting part of how to get this to work:


Solution

In order to create the web query that will provide us with historical stock (closing) prices, we need to supply Yahoo some information:

  1. Stock Symbol (Mandatory)
  2. Date Range (Optional, if not provided it will return all data available)
  3. Internal (Optional, defaults to days if not provided)

The URL is composed as follows (step by step):

  • Starting URL:
    • http://ichart.yahoo.com/table.csv?
  • Stock Symbol
    • http://ichart.yahoo.com/table.csv?s=AAPL
  • Starting Date
    • Month (goes from 0 to 11, don’t ask me why. So if you want July which is the seventh month of the year, you need to supply 06)
      • http://ichart.yahoo.com/table.csv?s=AAPL&a=0
    • Day
      • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1
    • Year
      • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010
    • Ending Date
      • Month (again, goes from 0 to 11)
        • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11
      • Day
        • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31
      • Year
        • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015
      • Interval
        • Here you supply one of the three trading periods supported:
Name Tag
Daily d
Weekly w
Monthly m
  • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m
  • And let’s tell Yahoo we want this as a CSV file
    • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m&ignore=.csv

We’re done. Now, if you click the URL: https://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m&ignore=.csv you will start the download of your CSV file containing all the historical prices for Apple (AAPL) since 2010 through the end of 2015. Did you notice how I used https instead of http? Yahoo Finance supports https if you want to have your queries protected, which I recommend.


Results and Conclusions

Now, let’s study some sample the output:

Date Open High Low Close Volume Adj Close
04/01/2010 213.429993 215.589996 190.25 192.059998 215986100 25.547121

Let’s go column by column:

  • Date
    • This is the date for which the values correspond
  • Open
    • The opening price for APPL for the given period/interval (week in this case)
  • High
    • The highest price for AAPL for the given period/interval (week in this case)
  • Low
    • The lowest price for AAPL for the given period/interval (week in this case)
  • Close
    • The closing price for AAPL for the given period/interval (week in this case)
  • Volume
    • The trading volume for AAPL for the given period/interval (week in this case)
  • Adjusted Close (Adj Close)
    • The closing price, adjusted for splits and the like. For example, there was a 7:1 split in Apple, so the adjusted close is less than 1/7th of the actual closing price back in 2010. I believe besides splits; it also considers dividends in the adjusted price. If you look at the price between 1/10/2015 and 2/11/2015, you can see there is a 0.509338 price differential which is very close to the .52 dividend paid.

UPDATE (May 20th, 2017)

Some sad news for users of Yahoo Finance to obtain free stock quotes: Yahoo has changed the way you form the URL and has made it somewhat more difficult for you to use the service. I think for most cases it is not a viable service anymore. As reported by some of the comments, now the URL requires a parameter called CRUMB which is obtained from the cookies set when establishing a session with Yahoo. In other words, that crumb is unique to your session.

Your options:

1) You can do something like this:

Parse the HTML when you make a query using the web page: Example URL: https://finance.yahoo.com/quote/SAN/history?period1=1483250400&period2=1495256400&interval=1d&filter=history&frequency=1d

2) Use the new URL and somehow get your cookie and obtain the CRUMB:

What do we know?

Yahoo changed their URL scheme to require an established session with cookies. Potentially you could use ‘curl’ to establish a connection to the main site, get a cookie, and then use that to get a crumb. With that crumb, now you can form your download URL. Because the Crumb is part of the cookie, it doesn’t change but is part of your browser so it won’t work on a different browser with a different cookie. Because of that, you might get an error like this:

{
    "finance": {
        "error": {
            "code": "Unauthorized",
            "description": "Invalid cookie"
        }
    }
}

Do note as well that times changed to POSIX/UNIX timestamps. You’ll need to convert your dates now.

3) Use download.finance.yahoo.com instead

A reader in the comments suggested it and it seems I was able to figure out how to use the API there. There is a catch of course: No historical prices… at least I wasn’t able to see an option for it. If you manage to get historical prices from Yahoo please share with us via the comments section. If this works for you, check out my post: How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too) Part II

4) Use Google instead

This probably means that for regular users Yahoo Finance is no longer a viable option to obtain stock quotes in Excel. I also can’t seem to be able to access https://finance.yahoo.com/quote/GOOG/history?p=GOOG on the excel web browser. My guess is that Yahoo has blocked access for the Excel client (very sneaky.) At this point my suggestion would be to use Google Finance. I’ll try to put together a post and reference it here so people can convert over.

yahoo finance and csv

By , October 31, 2017 2:03 PM

Modern broker portals offer an investor the opportunity to call up prices of his shares or options. There he usually also finds price charts and chart analysis charts at different times. For some private investors, these analyzes may not be sufficient enough. There may be computer scientists and hobby programmers among them who express a wish to use mathematical analysis with price data to calculate indicators themselves. But how does a programmer get the current and historical numbers of individual stock prices? This article aims to explain the possibilities of the Yahoo! Finance interface.
For determining different price data provides Yahoo! Finance ( for Germany) various CSV export services. CSV stands for “Comma Separated Values” and means nothing else here than that the entire key figures (such as closing price or volume) are delivered in pure text form separated by commas and line breaks. Data in such a format can easily process a computer program for analysis purposes. In the following, the known interfaces are explained in more detail and suitable examples and PHP scripts are presented. Since I do not know any official API of Yahoo! Finance and I have determined the interface described here by trial and error, I can not guarantee the completeness of the shown range of functions. For hints I am therefore very grateful. Does a developer use the services of Yahoo!Read the terms of use ( see also ) carefully.

Determine current price data

Quite frankly, “up-to-date” means a 15-minute delay for NASDAQ values ​​on the free Yahoo! Finance interface and up to 60 minutes for other exchanges ( more on Yahoo! Finance). But for most uses or western stock prices, this lag should not be a problem. Yahoo! offers courses from different stock exchanges. The selection of the corresponding trading place takes place in the URL itself, as well as in an appendix (suffix) at the symbol indication. The chapter “Country Differences” lists the differences. For a better understanding, I show here only the base URL, which is mainly valid for the US stock market prices. The result is a list of values, separated by commas. The service addresses for current price information is http://finance.yahoo.com/d/quotes.csv?(alternative http://download.finance.yahoo.com/d/quotes.csv?) and accepts the following three known parameters:

parameter description
s = The symbol of the security is specified with the parameter “s”. For example, here ” s=AAPL” provides the current price data from Apple Inc. A combination of multiple symbols separated by a plus sign is also possible (for example, ” s=AAPL+MSFT” for Apple and Microsoft). The values, separated by commas, are then each returned to a separate line per symbol.
f = The format codes are specified here with the parameter “f”. This is a text with a number of concatenated shortcuts. For example, the character “s” indicates that the symbol of the stock should be issued. “N” returns the name, “a” the current request course. The values ​​are output exactly in the order requested here. A list of all known format codes is listed below in the chapter “The Format Codes”. Example:f=sna
e = csv The export type is specified with the parameter “e”. You should always e=.csvsubmit ” ” for a Comma Separated Values ​​result here. Experience has shown that this parameter is probably optional.

The following example uses Apple and Microsoft to determine the symbol, company name, current request rate, and associated time. Time and market value are always country-specific (US time and dollar). If you enter this URL in the browser, you can save the result. Then you can open and view the raw data in a text editor (do not open with Excel, as the formatting will not be apparent here).

http://finance.yahoo.com/d/quotes.csv?s=AAPL+MSFT&f=snat1
---
"AAPL", "APPLE INC", 151.47, "3:31 pm"
"MSFT", "MICROSOFT CP", 29.09, "3:31 pm"

A program can now store this data in a multi-dimensional array. The columns contain the contents of the values ​​requested by the format codes. The lines each represent the number tuple of a security. A PHP function for determining price data could thus look like this:

<? Php
    / *
     * Getting current quotes:
     * Reading multilingual lines of values ​​in an array on the given symbols and function tags.
     Each line holds the values ​​of one symbol. In the argument, symbols are separated by "+".
     *
     * (c) Matthias Brusdeylins, 2008
     * License: CC-GNU GPL (http://creativecommons.org/licenses/GPL/2.0/)
     * /    
    define ("QUOTES_URL", "http://finance.yahoo.com/d/quotes.csv?");
    function loadYahooQuotes ($ symbol,
                              $ Tags)
    {
        $ lineCount = 0;
        $ stocks = array ();
        // load the stock quotes: we're opening it for reading
        // http://finance.yahoo.com/d/quotes.csv?s= STOCK SYMBOLS & f = FORMAT TAGS
        $ URL = QUOTES_URL. "S = $ symbol & f = $ tags & e = .csv";
        $ fileHandle = fopen ($ URL, "r");
        if ($ fileHandle) {
            // use the fgetcsv function to store quote into an array $ lineValues
            // one symbol in one line
            do {
                $ stockValues ​​= fgetcsv ($ fileHandle, 999999, ",");
                if ($ stockValues) {
                    $ Line count ++;
                    $ stocks [$ lineCount] = $ stockValues;
                }
            } while ($ stockValues);
        fclose ($ fileHandle);
        } else {
            // ERROR message in the array
            $ stocks [0] [0] = "ERROR";
            $ stocks [0] [1] = "No data found.";
        }
        return $ stocks;
    }
?>

currency

You can not only ask for individual stock prices, but also foreign exchange rates. To do this, you must pass a special construct of the form as a symbol parameter at the address presented above AB=X. Here, the letter stands Afor the first currency to be compared and the letter Bthen, of course, for the second currency. Xprobably stands for Exchange and will not be replaced. If you want to query several currencies, several constructs can be linked +together using the plus sign ” “. The following example should clarify this:

http://quote.yahoo.com/d/quotes.csv?s=USDCAD=X+JPYUSD=X&f=nl1d1t1
---
"USD to CAD", 1.0088, "4/4/2008", "5:02 pm"
"JPY to USD", 0.0098, "4/4/2008", "5:26 pm"

The abbreviations of the currencies consist of three letters and are listed here:

country contraction
Afganistan Afghani AFA
Albanian Lek ALLES
Algerian dinar DZD
Argentinean peso ARS
Aruban Florin AWG
Australian dollars AUD
Austrian shilling ATS
Bahraini dinar BHD
Bangladesh taka BDT
Barbados dollars BBD
Belgian Franc BEF
Belize dollars BZD
Bermuda dollars BMD
Bhutan Ngultrum BTN
Bolivian Boliviano BOB
Botswana Pula BWP
Brazilian Real BRL
British pound GBP
Brunei dollars BND
Bulgarian lev BGN
Cambodian Riel KHR
Canadian dollars CAD
Cape Verde Escudo CVE
Cayman Islands Dollars KYD
CFA franc (BCEAO) XOF
CFA franc (BEAC) XAF
CFP francs XPF
Chilean Peso CLP
Colombian Peso COP
Comoros franc KMF
Costa Rican Colon CRC
Croatian Kuna HRK
Cuban Peso CUP
Cypriot Pound CYP
Czech Koruna CZK
Danish crown DKK
Djibouti franc DJF
Dominican peso DOP
Dutch Guilder NLG
East Caribbean Dollars XCD
Egyptian Pound EGP
El Salvador Colon SVC
Estonian Kroon EEK
Ethiopian Birr ETB
Euro EUR
Fiji dollars FJD
Finnish Markka FIM
French franc FRF
Gambia Dalasi GMD
German Mark THE
Ghanaian Cedi GHC
Gibraltar Pound GIP
Greek Drachma GRD
Guatemala Quetzal GTQ
Guinea franc GNF
Guyana dollars GYD
Haitian Gourde HTG
Honduras Lempira HNL
Hong Kong dollars HKD
Hungarian Forint HOOF
Iceland Krona ISK
Indian rupee INR
Indonesian Rupiah IDR
Irish punt IEP
Israeli shekel ILS
Italian Lira ITL
Jamaican dollars JMD
Japanese yen JPY
Jordanian Dinar IODINE
Kenyan Shilling KES
Kuwaiti Dinar KWD
Laos kip LAK
Latvian lats LVL
Lebanese Pound LBP
Lesotho Loti LSL
Lithuanian Litas LTL
Malagasy Franc MGF
Malawi Kwacha MWK
Malaysian Ringgit MYR
Maldives Rufiyan MVR
Maltese Pound MTL
Mauritania Ouguiya MRO
Mauritius Rupee MUR
Mexican peso MXN
Mongolian Tugrik MNT
Moroccan dirham MAD
Mozambique Metical MZM
Myanmar Kyat MMK
Namibian dollars NAD
Nepal Rupee NPR
Netherlands Antilles Guilder ANG
New Zealand dollars NZD
Nicaraguan Cordoba NIO
Nigerian Naira NGN
Norwegian Krone NOK
Oman Rial OMR
Pakistani Rupee PKR
Papua New Guinea Kina PGK
Peruvian Sol PEN
Philippines peso PHP
Polish zloty PLN
Portuguese Escudo PTE
Qatari Rial QAR
Renmimbi Yuan CNY
Romanian leu ROL
Russian Ruble RUB
Salomon Islands Dollar SBD
Sao Tome & Principe Dobra HOURS
Saudi Arabian Riyal SAR
Seychelles Rupee SCR
Sierra Leone Leone SLL
Singapore dollars SGD
Slovak Koruna SKK
Slovenian tolar SIT
South African Rand ZAR
South Korean won KRW
Spanish peseta ESP
Sri Lanka Rupee LKR
St. Helena Pound SHP
Sudanese dinar SDD
Suriname Guilder SRG
Swaziland Lilangeni SZL
Swedish Krona SEK
Swiss Franc CHF
Syria Pound SYP
Taiwan New Dollar TWD
Tanzanian Shilling TZS
Thai baht THB
Tonga Isl Pa’anga TOP
Trinidad dollars TTD
Tunisian Dinar TND
Turkish Lira TRL
Ugandan Shilling UGX
Ukraine Hryvnia UAH
United Arab Emirates Dirham AED
US dollar USD
Vanuatu Vatu VUV
Venezuelan Bolivar VEB
Vietnam Dong VND
Western Samoa Tala WST
Zambia Kwacha ZMK
Zimbabwean dollars ZWD

The format codes

There are a number of accepted format tags available for the format code parameter (“f”). Many are probably redundant and are simply summarized here (eg “a” and “b2” for request course). The following table lists all known codes.
Attention: In some cases, Yahoo! Finance returns multi-digit values, also separated by commas. This, in itself, is awkward, since it will be difficult for you as a programmer to distinguish the commas between each value from the commas in one value. Here I recommend to query these parameters separately.

code importance
a, a0, a4 Ask
a1 – unknown –
a3, b2 Ask (Realtime) – with Pre and Post Market
a2 Average Daily Volume
a5 Please note: here the value may be returned in groups of numbers, separated by a comma! The number of comma separated groups varies.
b, b0 Bid
b2, a3 Ask (Realtime) – with Pre and Post Market
b3, b1 Bid (Realtime) – with Pre- and Post-Market
b4 Book Value per Share
b6 Bid Size Attention: here the value under certain circumstances in number groups, separated by a comma is returned! The number of comma separated groups varies.
c, c2, q1 Change & Percent Change
c0 Change in Percent
c1, c7 Change
c3 Commission
c4 Currency
c5 – unknown –
c6 Change (Realtime)
c8 After Hours Change (Realtime)
d, d0 Trailing Annual Dividend Yield (Dividend per Share)
d1 Last Trade Date
d2 Trade Date
d3 Last Trade Date (text)
e, e0 Earnings per Share (Diluted EPS)
e1 Error Indication (returned for symbol changed / invalid)
e2 Expiration date on warrants
e3 – unknown –
e4 – unknown –
e7 EPS Estimate Current Year
e8 EPS Estimate Next Year
e9 EPS Estimate Next Quarter
f, f0 Trade Links Additional
f1, f2, f3, f4, f5, g2 – HTML fragment with company name, market place and trade link –
f6 Float Shares Attention: here the value may be returned in number groups, separated by a comma! The number of comma separated groups varies.
f7, f8, – HTML fragment with trade link –
g, g0 Day’s Low
g1 Holdings Gain Percent
g3 Annualized Gain
g4 Holdings Gain
g5 Holdings Gain Percent (Realtime)
g6 Holdings Gain (Realtime)
h, h0 Day’s High
h1, h2, h3, h4, h5, h6 – HTML Fragment –
i, i0 More Info
i2 – unknown –
i3 – unknown –
i4 – unknown –
i5 Order Book (Realtime)
i6 – unknown –
i7, l1, l7, l9, q2, v2, y2 Last Trade
j, j0 Year Low (52-week)
j1 Market capitalization
j2 Shares Outstanding Attention: here the value under certain circumstances in number groups, separated by a comma is returned! The number of comma separated groups varies.
j3 Market Capitalization (Realtime)
j4 EBITDA
j5 Change From Year Low (52-week)
j6 Percent Change From Year Low (52-week)
k, k0 Year High (52-week)
k1 Last Trade With Time (Realtime)
k2 Change in Percent (Realtime)
k3 Last Trade Size Attention: here the value under certain circumstances in number groups, separated by a comma is returned! The number of comma separated groups varies.
k4 Change From Year High (52-week)
k5 Percent Change From Year High (52-week)
l, l0, n1, n5, y1 Last Trade with Time
l1 Last Trade (Price Only)
l2 High limit
l3 Low limit
l4 – unknown –
l5 – unknown –
l6 – unknown –
m, m0 Day’s Range
m1 – unknown –
m2 Day’s Range (Realtime)
m3 50-day moving average
m4 200-day moving average
m5 Change From 200-day Moving Average
m6 Percent Change From 200-day Moving Average
m7 Change From 50-day Moving Average
m8 Percent Change From 50-day Moving Average
n, n0 Surname
n2 – unknown –
n3 – unknown –
n4 Notes
n6 – unknown –
n7 – unknown –
n8 – unknown –
n9 – unknown –
o, o0 Open
o1, o2 Open interest on warrants
o4 – unknown –
o5 – unknown –
p, p0, p8 Previous Close
p1 Price Paid
p2, p4 Change in Percent
p3 – unknown –
p5, p9 Price Sales
p6 Price Book
q, q0 Ex-dividend date
q3 – HTML fragment for period selection –
q4 – HTML fragment for selection of the type of graph –
q5 – HTML fragment for selection of scaling –
q6 – HTML fragment for chart size selection –
q7 – HTML fragment for selection of Moving Avg. –
q8 – HTML fragment for EMA selection –
q9 – HTML fragment for comparison form –
r, r0 P / E Ratio
r1 Dividend Pay Date
r2 P / E Ratio (Realtime)
r3 – HTML fragment for selection of indicators –
r4 – HTML fragment for selection of overlays –
r5 PEG ratio
r6 Price EPS Estimate Current Year
r7 Price EPS Estimate Next Year
r9 – unknown –
s, s0, s2, s4 symbol
s1 Owned Shares
s3 Subscription price for warrants
s6 Revenue
s7 Short Ratio
t, t0, t2, u, u0, u2, u3 HTML fragment for a chart image
t1 Last Trade Time
t3 – unknown –
t4 – unknown –
t5 – unknown –
t6 Trade Links (HTML)
t7 Ticker trend
t8 1 year target price
v, v0, v6 Volume
v1 Holdings Value
v3 – unknown –
v4 – unknown –
v5 – unknown –
v7 Holdings Value (Realtime)
w, w0 Year Range (52-week)
w1 Day’s Value Change
w4 Day’s Value Change (Realtime)
x, x0 Stock Exchange
y, y0 Trailing Annual Dividend Yield In Percent
z2 – HTML Fragment –
z3 – returns “Oops!” –

Higher index values ​​only result in a repetition of the contents. Presumably, the codes should be two digits (letter + number). If the number falls away, we automatically assume the index 0.

Daytrading data

Unfortunately, Yahoo! Finance only offers the last 100 daily market values ​​at the time of the investigation. Again, the delay of 15-60 minutes, as described in the section “Current price data”. The service address for the daytrading data is http://logtrade.finance.vip.ukl.yahoo.com/lastTrades?and accepts the following three known parameters:

parameter description
s = The symbol of the security is specified with the parameter “s”. For example, here ” s=AAPL” provides the last 100 day trading data from Apple Inc.
output = user The meaning of this parameter is unfortunately not yet known. For more information, I am very grateful.
i = eu Again, the meaning of the parameter is not known to me.

The result of such a query returns a list of value groups – a group for each trade. Such a tuple contains the trading date, the market value and the volume of the trade.

Historical price data

For a security, historical data is also available at Yahoo. The service URL for this is http://ichart.finance.yahoo.com/table.csv?(alternatively:) http://real-chart.finance.yahoo.com/table.csv?and has parameters for the icon, as well as the time interval within which a developer wants to find the daily or weekly quote data:

parameter description
a = Start month -1 (January is the value “0” here)
b = Start tag
c = Start Year
d = End month -1 (January is the value “0” here)
e = End tag
f = End-Year
g = Chart grading (d = daily values, w = weekly values, m = monthly values, v = dividend distributions)
s = Symbol of the stock price
ignore = .cvs (constant value, but optional)
y = Shift by a specified number of days / weeks / months. Does not really make sense, since already above an interval was specified. Nevertheless, for example, the volume values ​​of a month (g = m) differ if a shift Y> 0 is specified here. For further information, I am grateful here!

The result consists of a list of daily, weekly or monthly values ​​for the submitted period. The first line contains the heading of each column value. In all other lines, Yahoo! Finance then returns the date, the opening price, the highest and the lowest price, as well as the daily closing price and the trading volume for each individual day. Because these values ​​are unadjusted to splits and dividends, Yahoo! in the last column “Adj. Close “still adjusted closing prices. The result looks a little bit different if the chart gradation shows the value “v” for the dividend distributions. Then only two columns with a date and the value itself are returned.

http://ichart.finance.yahoo.com/table.csv?s=AAPL&d=3&e=3&f=2008&g=d&a=8&b=7&c=1984&ignore=.cvs
---
Date, Open, High, Low, Close, Volume, Adj Close
2008-04-02,148.78,151.20,145.85,147.49,37253700,147.49
2008-04-01,146.30,149.66,143.61,149.53,36846400,149.53
2008-03-31,143.27,145.71,142.52,143.50,27418300,143.50
2008-03-28,141.80,144.65,141.60,143.01,25521800,143.01
...
http://itable.finance.yahoo.com/table.csv?s=AAPL&d=3&e=3&f=2008&g=v&a=8&b=7&c=1984&ignore=.cvs
---
Date, Dividends
1995-11-21,0.030000
1995-08-16,0.030000
1995-05-26,0.030000
...

For the programmatic evaluation of this data, the array can be used as a result. In each row, the values ​​of a day (or a week, a month or the dividends) can be stored. The following PHP function should clarify this:

<? Php
    / *
     * Getting historical quotes in a given time period
     * Parameters:
     * $ symbol: symbol
     * $ startDay, $ startMonth, $ startYear: the first day for the historical quotes
     * $ endDay, $ endMonth, $ endYear: the last day for the historical quotes
     * $ step (d / w / m / v): d = daily, w = weekly, m = monthly, v = dividends
     *
     * (c) Matthias Brusdeylins, 2008
     * License: CC-GNU GPL (http://creativecommons.org/licenses/GPL/2.0/)
     * /
    define ("HISTORY_QUOTES_URL", "http://ichart.yahoo.com/table.csv?");
    function loadHistoricalYahooQuotes ($ symbol,
                                        $ StartDay,
                                        $ Start month,
                                        $ Startyear,
                                        $ EndDay,
                                        $ EndMonth,
                                        $ EndYear,
                                        $ Step)
    {
        $ lineCount = 0;
        $ days = array ();
        // modify parameters
        $ Ms = $ start month-1;
        $ EM = $ endMonth-1;
        // load the historical stock quotes: we are opening it for reading
        // http://ichart.yahoo.com/table.csv?s= SYMBOL.COUNTRY & a, b, c START $ d, e, f END $ gd = daily / w = weekly / m = monthly / v = dividends
        $ URL = HISTORY_QUOTES_URL. "S = $ symbol & a = $ sM & b = $ startDay & c = $ startYear & d = $ eM & e = $ endDay & f = $ endYear & g = $ step & y = 0 & ignore = .cvs";
        $ fileHandle = fopen ($ URL, "r");
        if ($ fileHandle) {
            // use the fgetcsv function to store quote into an array $ lineValues
            // store one icon in one line
            do {
                $ stockValues ​​= fgetcsv ($ fileHandle, 999999, ",");
                if ($ stockValues) {
                    $ Line count ++;
                    $ days [$ lineCount] = $ stockValues;
                }
            } while ($ stockValues);
            // close handle
            fclose ($ fileHandle);
        } else {
            $ days [0] [0] = "ERROR";
            $ days [0] [1] = "No data found.";
        }        
        return $ days;
    }
?>

Country differences

Not only shares in different currencies are traded on the stock exchange, but also small caps such as warrants. The trading of such securities is usually limited to one country or stock exchange. For this reason, Yashoo! Finance also offers different service addresses. In this article so far only the addresses for US stock market data have been shown. Thus, under the URL, http://finance.yahoo.com/d/quotes.csv?the current price of a stock in US dollars can be determined. If, on the other hand, you want to find out a course from Germany, the URL is available.http://de.finance.yahoo.com/d/quotes.csv?
The following table shows the URLs to the Yahoo! Finance service for current stock quotes. The first column also lists the symbol suffixes that can be added to the symbol for an active stock market choice (eg ” AAPL.DE” for the XETRA price of the Apple stock). A visit to the Yahoo! Finance helps to choose the right symbol.

Country & Suffix URLs
Argentina
.BA (Buenos Aires)
http://finance.yahoo.com/d/quotes.csv?
http://ar.finance.yahoo.com/d/quotes.csv?
Australia
.AX (Australia)
http://au.finance.yahoo.com/d/quotes.csv?
Belgium
.BR (Brussels)
http://finance.yahoo.com/d/quotes.csv?
Brazil
.SA (São Paulo)
http://finance.yahoo.com/d/quotes.csv?
http://br.finance.yahoo.com/d/quotes.csv?
Denmark
.CO (Copenhagen)
http://finance.yahoo.com/d/quotes.csv?
http://dk.finance.yahoo.com/d/quotes.csv?
Germany
.BE (Berlin-Bremen)
.DU (Düsseldorf)
.F (Frankfurt)
.HM (Hamburg)
.HA (Hanover)
.MU (Munich)
.SG (Stuttgart)
.DE (XETRA)
http://de.finance.yahoo.com/d/quotes.csv?
China
.SS (Shanghai)
.SZ (Shenzhen)
http://finance.yahoo.com/d/quotes.csv?
http://cn.finance.yahoo.com/d/quotes.csv?
England
.L (London)
http://uk.finance.yahoo.com/d/quotes.csv?
France
.PA (Paris)
http://finance.yahoo.com/d/quotes.csv?
http://fr.finance.yahoo.com/d/quotes.csv?
Hong Kong
.HK (Hong Kong)
http://hk.finance.yahoo.com/d/quotes.csv?
India
.BO (Bombay)
.NS (National Stock Exchange India)
http://in.finance.yahoo.com/d/quotes.csv?
Indonesia
.JK (Jakarta)
http://sg.finance.yahoo.com/d/quotes.csv?
Ireland
.IR (Irish)
http://uk.finance.yahoo.com/d/quotes.csv?
Israel.
TA (Tel Aviv)
http://finance.yahoo.com/d/quotes.csv?
Italy
.MI (Milano)
http://finance.yahoo.com/d/quotes.csv?
http://it.finance.yahoo.com/d/quotes.csv?
Canada
.TO (Toronto)
.V (TSX Venture Exchange)
http://ca.finance.yahoo.com/d/quotes.csv?
Korea (South)
.KS (Korea)
.KQ (KOSDAQ)
http://kr.finance.yahoo.com/d/quotes.csv?
Malaysia
.KL (Kuala Lumpur)?
http://sg.finance.yahoo.com/d/quotes.csv?
Mexico
.MX (Mexico)
http://finance.yahoo.com/d/quotes.csv?
New Zealand
.NZ (New Zealand)
http://au.finance.yahoo.com/d/quotes.csv?
Netherlands
.AS (Amsterdam)
http://finance.yahoo.com/d/quotes.csv?
Norway
.OL (Oslo)
http://finance.yahoo.com/d/quotes.csv?
http://no.finance.yahoo.com/d/quotes.csv?
Austria
.VI (Vienna)
http://finance.yahoo.com/d/quotes.csv?
Pakistan
.KA (Karachi)?
http://finance.yahoo.com/d/quotes.csv?
Portugal
.LS (Lisbon)
http://finance.yahoo.com/d/quotes.csv?
Sweden
.ST (Stockholm)
http://finance.yahoo.com/d/quotes.csv?
http://se.finance.yahoo.com/d/quotes.csv?
Switzerland
.SW (Swiss)
.VX (Virt-X)
http://finance.yahoo.com/d/quotes.csv?
Singapore
.SI (Singapore)
http://sg.finance.yahoo.com/d/quotes.csv?
Spain
.BC (Barcelona)
.BI (Bilbao)
.MF (Madrid Fixed Income Market)
.MC (Madrid SE CATS)
.MA (Madrid)
http://es.finance.yahoo.com/d/quotes.csv?
Taiwan
.TWO (OTC Taiwan Stock Exchange)
.TW (Taiwan)
http://sg.finance.yahoo.com/d/quotes.csv?
Thailand
.BK (Bangkok)
http://sg.finance.yahoo.com/d/quotes.csv?
USA
no suffix for AMEX, NASDAQ and NYSE
.OB (OTC Bulletin Board Market)
.PK (Pink Sheets)
http://finance.yahoo.com/d/quotes.csv?

download

The class Finance_API_Yahooprovides easy access to the interface described here. It can be used to determine current and historical price data. The result is returned in a two-dimensional array (see examples in the text). The Zend framework served as a template for the package structure . Thus, a simple integration is guaranteed in this.
Yahoo! Finance API, version 1.0

Change Log

0.5 31.03.2008 – First release
0.6 06.04.2008 – Function code list completed
1.0 04.05.2008 – Zend Framework package structure integrated

CC-GNU GPL
This software and the pieces of code shown here are covered by the CC-GNU GPLlicense agreement .

67

Comments

  1. JSTHEMASTER  October 11, 2008
    Parameters for Warrants:
    s3 = Strike
    e2 = Expiry
    o1 = o2 = Open Intrest
    Regards,
    JSTHEMASTER!

    reply

  2. Matthias Brusdeylins   October 12, 2008
    Thank you for the informations !

    reply

  3. Blear  June 16, 2009
    Thanks a lot for the great documentation.
    And how do I do that with the raw material prices?
    Example: Oil price development of recent years?
    does anyone know the parameters? Thank you.

    reply

  4. Matthias Brusdeylins   June 17, 2009
    Is not that just another symbol?
    Eg “OIL” or “USO” or “DTO” …
    http://download.finance.yahoo.com/d/quotes.csv?s=DTO&f=sl1d1t1c1ohgv&e=.csv

    reply

  5. Paul  July 8, 2009
    I only want to query the 30 DAX shares daily. But the DAX composition changes again and again. Would there be a possibility here instead of specifying the 30 individual titles and constantly adapting them to read out a list of current DAX shares? Does anyone have an idea how to do that?

    reply

  6. tunix  July 30, 2009
    @ Paul
    here the link for Daxwerte / list
    http://de.old.finance.yahoo.com/d/quotes.csv?s=@%5EGDAXI&f=sl1d1t1c1ohgv&e=.csv

    reply

  7. Sven  August 31, 2009
    How do I ask for the current copper prices? Or. what is the symbol

    reply

  8. ibuddy  August 31, 2009
    Hello Matthias Brusdeylins!
    I would like to thank you very much, you have gathered here a very nice collection and that helps me immensely!
    Maybe you could have been more specific about the “@” for indexes. For example, delivers
    http://de.old.finance.yahoo.com/d/quotes.csv?s=@ ^ TECDAX & f = sn & e = .csv
    the + name of the TecDAX and you could easily fill its database automatically …. but only marginally.
    Do you know where to get a list of all doped stocks as CSV with symbol + name? Then you could save the search on Yahoo!
    I take off my hat, very nice! Thank you very much!
    Best regards, ibuddy

    reply

  9. Alexx  September 1, 2009
    Very interesting article, thank you!
    What I have been puzzling for a long time: How can I use the parameters and the returned values ​​to determine whether the stock market is still open on which the stock is traded? Say whether the retrieval of the courses already happens after Close.
    The background is, unfortunately, I do not know all the opening times of all exchanges by heart …
    A thousand thanks.

    reply

  10. Me.MyBase  October 2, 2009
    Hello,
    is this DAX synopsis available for other indexes? So for MDAX, SDAX and TechDax or even for the international indices?

    reply

  11. Me.MyBase  October 2, 2009
    oh, has done. Refresh helps sometimes …😉

    reply

  12. chris  November 13, 2009
    Hi,
    With the foreign exchange no absolute and percentage changes
    are displayed, have already tried different things.
    Does anyone continue?
    http://download.finance.yahoo.com/d/quotes.csv?s=EURUSD=X&f=l&f=c

    reply

  13. bartmann_61  December 8, 2009
    Hello Matthias,
    very nice compilation; Thank you very much.
    @ Alexx
    The trading hours and especially trading days should be known in advance. I had missed a Chinese holiday; that was really trouble. The information is best obtained through the websites of the respective banks.
    A very good compilation of important stock exchanges can be found on
    http://www.six-swiss-exchange.com/trading/information/calendar/2009/grid_de.html
    The trading hours for eg SIX Zurich can be found on
    http: //www.six -swiss-exchange.com/trading/information/trade/on_order_book/trading_hours_order_de.html
    Similar information is available for all other exchanges on their sites.

    reply

  14. Godot  December 22, 2009
    Nice page!
    y2, v2, q2, l7, l9, l1 seem to spend the current price of funds.
    Thank you,
    Godot

    reply

  15. Warren Buffett   January 22, 2010
    Very good setting up, but now my question: Will I somehow get the balance sheet data via the API? They are also displayed on the side of Yahoo.
    Mario

    reply

  16. Developer  January 22, 2010
    Hello to you,
    the site is very nicely put together, compliment!
    However, I am still looking for a way to query Fondkusrs quite “desperately”. Somehow it should go (see Godot), but how exactly? For example, I’m interested in the Berenberg Globalway (ISIN: LU0123053893, WKZ: 603545). How do I query that? Can anyone help?
    Best regards,
    F.

    reply

  17. deridex   April 16, 2010
    Fund query seems to work with the normal query when fully qualifying the stock market traded on:
    http://www.finance.yahoo.com/d?s=618486.SG

    reply

  18. Vondor  July 12, 2010
    Always nice when someone takes time for such great instructions.
    I hope someday to make my own contribution.

    reply

  19. Max  August 17, 2010
    Thanks for this post, he helped me a lot!
    f = n outputs the name and f = s the character, is there a way to output the ISIN? (For example, if I look at the table of the TecDAX)
    Greetings and a big thank you

    reply

  20. Jan  October 7, 2010
    Hello,
    I agree with Mario alias Warren Buffet, very nice site, but is there a way to get the balance data displayed or come for any licensing reasons from Reuters and may not be downloaded?
    Greetings,
    J

    reply

  21. Matthias Brusdeylins   October 7, 2010
    If anyone has a solution here, I would be very grateful.

    reply

  22. Jan  October 11, 2010
    It remains probably only HTML parsing left …

    reply

  23. Max  October 27, 2010
    ehm …
    … am I doing something wrong or is it no longer possible to spend the 50 or 200-day average?
    For example:
    http://en.old.finance.yahoo.com/d/quotes.csv?s=@%5ETECDAX&f=sl1d1t1c1ohgvm3&e=.csv
    If I omit the “old”, I get an average – the other values ​​seem but all not to be correct anymore.
    greeting

    reply

  24. Jochen  October 28, 2010
    Hi,
    how would I get the course or the metal quotation for silver in a CSV?
    If I pass “AG” (for silver) as a parameter, I get the course from 1.5.2009 but no current.
    http://en.old.finance.yahoo.com/d/quotes.csv?s=AG&f=sl1d1t1c1ohgv&e=.csv
    What am I doing wrong?
    Greetings
    Jochen

    reply

  25. fred  November 3, 2010
    ingenious!

    reply

  26. Kevin R.  November 6, 2010
    THANKS !!!! That’s all what I need !!!

    reply

  27. Mark  December 9, 2010
    @Jochen
    is a little late, but better late than never.
    The URL for silver is:
    http://en.old.finance.yahoo.com/d/quotes.csv?s=SLV&f=snat1&e=.csv
    You have to go to the Yahoo page, I’ll call it ID’ s, pick. Had tried at the beginning with AG for silver and AU for gold, unfortunately, does not work.
    greeting

    reply

  28. Rene  July 8, 2011
    First of all, thanks for the contribution ..
    what does it look like because of ISIN .. grieve you somehow out?

    reply

  29. Michael  August 12, 2011
    Great description! Thank you. But also have a question: Why does not the API provide values ​​for the Dow Jones?
    http://finance.yahoo.com/d/quotes.csv?s= ^ ixic + ^ gdaxi + ^ dji & f = l1np2
    At the moment I’m desperate for it …
    Greetings

    reply

  30. Matthias Brusdeylins   August 23, 2011
    Hi,
    so the historical price data of the Dow Jones I get:
    http://ichart.finance.yahoo.com/table.csv?s= ^ DJI & f = snat1
    ->
    Date, Open, High, Low, Close, Volume, Adj Close
    2011-08-19,10989.60,11099.26,10749.83, 10817.65,5167560000,10817.65
    2011-08-18,11406.27,11406.27,10830.59,10990.58,3234810000,10990.58
    2011-08-17,11392.01,11550.37,11306.18,11410.21,4388340000,11410.21
    unfortunately the current price data are empty:
    http://finance.yahoo.com/d/quotes.csv?s= ^ DJI & f = snat1
    -> Missing Symbols List.
    When I join Yahoo! Finance go to the following website:
    http://finance.yahoo.com/echarts?s= ^ DJI + Interactive # symbol = ^ DJI; range = 1d
    I find below in the gray box following note:
    “Quotes are updated automatically, but will be turned off after 25 minutes of inactivity. “
    Assumption: Here you must access the historical data after the trading hours …

    reply

  31. johannes november   17, 2011
    Thanks for the detailed documentation of the Yahoo Finance csv download links.
    The URL “http://logtrade.finance.vip.ukl.yahoo.com/lastTrades?” To retrieve the daytrading data does not work for me. Is it possible that Yahoo turned off this call? Do you have a working example for retrieving daytrading data?

    reply

  32. Tobias   November 17, 2011
    Halo.
    We develop an app for stock market data.
    But where do I get the data for raw materials like aluminum, steel, copper, etc?
    Thank you!

    reply

  33. Christian Herold   December 5, 2011
    Hello,
    current values ​​for Dow Jones are available under INDU without ^.
    http://en.finance.yahoo.com/d/quotes.csv?s=INDU&f=l1d1t1&e=.csv
    Regards
    Christian

    reply

  34. Ante  January 8, 2012
    Compliment. Very detailed instructions. Thank you!
    I have a general question.
    Does anyone know the reason why Yahoo Finance and zB Finanzen.net
    provide different data. Eg book value alliance. (96.46 vs. 93.78).
    Both values ​​seem to be in €.
    Thank you in advance.
    Greetings
    Ante

    reply

  35. Tim  February 5, 2012
    Hi, I think this function is very nice. Unfortunately, I have a big problem. I can not read the percent with p2. My return is always N / A. Is that related to the drawing? Hope you can help me. I always get N / A when I call p2 and / or t1.

    reply

  36. Bobfi  February 28, 2012
    Fantastic contribution, even if it already has a few days under its belt. Unfortunately, I can not figure out how to get the crude oil price (WTI and / or cash) in dollars. Does anyone have an idea?

    reply

  37. Thomas  February 29, 2012
    Very good contribution! Helped me a lot. Only the information on Daytrading are not quite up to date anymore. The following website shows how Yahoo can access current intraday courses or the intrady courses of the last 5 days:
    http: //www.marketcalls. in / AmiBroker / exploring-yahoo-real time-data-feed.html

    reply

  38. Ingo  August 5, 2014
    For me the German Abdragen do not work? What can this be? Is anyone still active here and can help me?
    LG
    Ingo

    reply

  39. Dolin  November 27, 2014
    From the whole presentation I am very pleased and very pleased with the download. For some symbols like z, b. NVDA or the Thai stock index, there seems to be no historical price data. For
    the Japanese shares, unfortunately, I was only able to obtain price data from the USA or the German stock exchanges, but not from Japanese stock exchanges. Thank you for the entire instructions!

    reply

  40. Dolin  November 27, 2014
    Thank you for all the really great instructions! Unfortunately, until now I have not been able to obtain the price data for Japanese stocks from a Japanese stock exchange, but only from German or US stock exchanges.

    reply

  41. Autonomous  December 20, 2014
    ^ IBEX and ^ DJI forgot yahoo … at http://finance.yahoo.com/d/quotes.csv?s=

    reply

  42. Robert  August 14, 2015
    Class description, I’ve been looking for a long time!
    Incidentally, there are also historical foreign exchange rates, but they only work from the point of view of the US dollar.
    Parameter s == X
    Example: http://ichart.finance.yahoo.com/table.csv?s=CNY=X&a=0&b=1&c=2015&d=7&e=1&f=2015&g=d&y=0&ignore=.csv
    returns the prices from Jan-Jul 2015 for USD -> CNY

    reply

  43. Philipp   March 9, 2016
    Thanks for the info which stock exchange symbol, which stock exchange represents. That’s what I was looking for. Real Super API.

    Now I can finally link my stock screener directly to Yahoo numbers (balance sheet / profit / loss ratios) and only let me see results that meet my desired criteria.

    reply

  44. robser  April 15, 2016
    Hello,

    who can show me how to download historical data of foreign exchange? The article (very nice) suggests that you can not download historical data from the Forex.

    Best regards

    reply

  45. tk   June 8, 2016
    Hello,

    thanks for the information.

    I adjusted the PHP script a bit because it was not fully functional yet.
    For anyone who wants to try it:

    de­fine („QUOTES_URL“, „http://finance.yahoo.com/d/quotes.csv?“);

    $sym­bol = „AAPL“;
    $tags = „snat1″;

    func­tion loadYahooQuotes($symbol,$tags)
    {
    $line­Count = 0;
    $stocks = ar­ray();

    $URL = QUOTES_URL.“s=$symbol&f=$tags&e=.csv“;

    $file­Handle = fo­pen ($URL,“r“);

    if ($file­Handle) {
    // use the fgetcsv func­tion to store quote va­lues into an ar­ray $li­ne­Va­lues
    // one sym­bol in one line

    do {
    $stock­Va­lues = fgetcsv ($file­Handle, 999999, „,“);

    if ($stock­Va­lues) {
    $line­Count++;
    $stocks[$lineCount] = $stock­Va­lues;
    }

    } while ($stock­Va­lues);
    fclose ($file­Handle);
    } else {
    // ERROR-Message in the ar­ray
    $stocks[0][0] = „ERROR“;
    $stocks[0][1] = „No data found.“;
    }
    re­turn $stocks;
    }

    loadYahooQuotes($symbol, $tags);

    reply

  46. Daniel   October 6, 2016
    Hello, does anyone know how to just get the historical dividend yield as an issue?
    Thanks and best regards
    Daniel

    reply

  47. Matthias  November 28, 2016
    Hi,

    I can not find the symbols for some warrants on the Yahoo page -> eg DE000DT5MP92 PUT / DAX PERFORMANCE INDEX

    I can not query without symbols. How or where do I find warrant data?

    reply

  48. Tim McTiger  December 10, 2016
    Hi,

    cool tutorial, helped me a lot.
    I am currently working on one in which I need the historical values ​​of raw materials.
    Can I also get them from the Yahoo API?

    Thanks and best regards
    Tim

    reply

  49. Joscha  January 5, 2017
    Hello,

    For me, the fopen function fails at this URL:

    http://real-chart.finance.yahoo.com/table.csv?s=AAPL&a=1&b=1&c=2000&g=d&y=0&ignore=.cvs

    Although this url correctly displays the data in the browser, fopen always returns only false.
    does anyone know this problem ??

    Sincerely,
    Joscha

    reply

  50. Klaus Müller  January 28, 2017
    Hello,

    Thanks for the great tutorial. It’s really nice how people like you bother to pass on your knowledge.
    I am currently working on a project where I need historical data on raw materials (iron ore, nickel, copper).
    Does anybody know, whether one can refer these also over the Finance API of Yahoo.

    Thanks in advance
    Klaus

    reply

  51. trys  May 9, 2017
    Thank you very much.

    I have been looking for this for a long time –
    and have been satisfied with the partial solutions that I have found out myself.
    This is now an incentive to try more.
    Thanks also to the commentators.

    Only the gaps and errors remain with yahoo – which unfortunately tend to increase.

    reply

  52. maiermuc  June 6, 2017
    Thank you for the detailed article.

    Unfortunately, the Historical Data API at Yahoo changed at the end of May 2017.
    Thus, for example: the following link from the article no longer works.
    https://ichart.finance.yahoo.com/table.csv?s=AAPL&d=3&e=3&f=2008&g=d&a=8&b=7&c=1984&ignore=.cvs

    reply

  53. Josef  July 1, 2017
    It seems to me that now finance.yahoo.com can no longer download historical data using a PHP program.

    reply

  54. Josef  July 1, 2017
    It seems to me, that now finance.yahoo.com no historical data in the csv format by means of a php program receives.

    reply

  55. Harry Espino  July 9, 2017
    Yahoo Finance API is not available anymore. I have moved to MarketXLS after this change, much more reliable data.

    reply

    • Angerer  October 7, 2017
      In rhe csv format is it a possibility to download by a php program or is it necessary to use Excel?

      reply

Yahoo finance and Excel Webservce numberValue

By , October 31, 2017 1:53 PM

I’ve been playing around with building some Stock Tracking tools for Excel and I’ve learned a lot about how to query data from Yahoo! Finances API to get it into Excel while it’s all fresh in my memory, I figured I would take some time and document some of the techniques I’ve been using in my spreadsheets.

Breakdown Of The Yahoo! Finance Query URL

The premise behind how you query stock information in your web browser ultimately boils down to the URL address.  Here is the standard URL address formula with two inputs (the Ticker Symbol and the Data Type).

https://download.finance.yahoo.com/d/quotes.csv?s= [Ticker Symbol] &f= [Data Type]

A Few Examples

To get this data from a web address into an Excel spreadsheet, we can use the WEBSERVICE() function. I’ll also wrap a NUMBERVALUE() function around to convert the import text from Yahoo! Finance into a numerical value we can use inside Excel.

Example 1: Pull The “Current Stock Price” for Microsoft’s Stock

=NUMBERVALUE(WEBSERVICE(“https://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1“))

Example 2: Pull The “Current Dividend” for Microsoft’s Stock

=NUMBERVALUE(WEBSERVICE(“https://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=d“))

Example 3: Pull “% change From 52 Week Low” for Microsoft

This one is a little bit trickier because the results from the url have a plus sign or a negative sign in front of the resulting % change which the NUMBERVALUE() function does not like. In the following formula, I am taking the result from Yahoo! finance and removing the first character (ie +/-) from the result.

=NUMBERVALUE(REPLACE(WEBSERVICE(“https://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=j6“),1,1,””))

Example 4: Link your Ticker Symbols to a Spreadsheet Cell

If you want to use the same formula for a bunch of different ticker symbols, you can link your formula using a cell reference. Here is the same formula from Example 1, however it is now point to a ticker symbol in cell A2.

=NUMBERVALUE(WEBSERVICE(“https://download.finance.yahoo.com/d/quotes.csv?s=” & A2 & “&f=l1“))

Data Type Tables

Yahoo! has a whole collection of data points you can pull about a specific ticker symbol. Here is a list of the variables and what they pull in:

Stock Pricing

Averages

Variable Description
a Ask
b Bid
b2 Ask (Real-time)
b3 Bid (Real-time)
p Previous Close
o Open
c1 Change
c Change & Percent Change
c6 Change (Real-time)
k2 Change Percent (Real-time)
p2 Change in Percent
d1 Last Trade Date
d2 Trade Date
t1 Last Trade Time

Dividends

Variable Description
y Dividend Yield
d Dividend per Share
r1 Dividend Pay Date
q Ex-Dividend Date
Variable Description
c8 After Hours Change (Real-time)
c3 Commission
g Day’s Low
h Day’s High
k1 Last Trade (Real-time) With Time
l Last Trade (With Time)
l1 Last Trade (Price Only)
t8 1-Year Target Price
m5 Change From 200 Day Moving Average
m6 % Change From 200 Day Moving Average
m7 Change From 50 Day Moving Average
m8 % Change From 50 Day Moving Average
m3 50-Day Moving Average
m4 200-Day Moving Average

Volume

Variable Description
v Volume
a5 Ask Size
b6 Bid Size
k3 Last Trade Size
a2 Average Daily Volume

52-Week Pricing

Ticker Related Information

Variable Description
k 52-Week High
j 52-Week Low
j5 Change From 52-Week Low
k4 Change From 52-Week High
j6 Percent Change From 52-Week Low
k5 Percent Change From 52-Week High
w 52-Week Range
g1 Holdings Gain Percent
g3 Annualized Gain
g4 Holdings Gain
g5 Holdings Gain Percent (Real-time)
g6 Holdings Gain (Real-time)
Variable Description
i More Info
j1 Market Capitalization
j3 Market Cap (Real-time)
f6 Float Shares
n Name
n4 Notes
s Symbol
s1 Shares Owned
x Stock Exchange
j2 Shares Outstanding

Financial Ratios

Variable Description
e Earnings per Share
e7 EPS Estimate Current Year
e8 EPS Estimate Next Year
e9 EPS Estimate Next Quarter
b4 Book Value
j4 EBITDA
p5 Price / Sales
p6 Price / Book
r P/E Ratio
r2 P/E Ratio (Real-time)
r5 PEG Ratio
r6 Price / EPS Estimate Current Year
r7 Price / EPS Estimate Next Year
s7 Short Ratio

Miscellaneous

Variable Description
t7 Ticker Trend
t6 Trade Links
i5 Order Book (Realtime)
l2 High Limit
l3 Low Limit
v1 Holdings Value
v7 Holdings Value (Realtime)
s6  Revenue
w1 Day’s Value Change
w4 Day’s Value Change (Realtime)
p1 Price Paid
m Day’s Range
m2 Day’s Range (Realtime)
c4 Currency

Refreshing The Formulas

As you might imagine, if you are pulling realtime data from Yahoo! Finance, Excel is not going to recalculate your WEBSERVICE() formulas every second. Therefore, you will need to manually recalculate your sheet (keyboard shortcut F9) in order to refresh your formulas.

Backing up VMware Workstation VMs

By , October 17, 2017 12:45 PM

Backing up VMware Workstation VMs

We run a small number of VM instances which are mostly sandbox type environments and hence backups are not always essential, but if you are running something a bit more crucial and wish to back up a VM regularly, what are your options? Running ‘traditional’ third party backup s/w within the guest OS is one approach (and adopted widely) and more VM-centric enterprise solutions such as VMware’s own VCB (VMWare Consolidated Backup) are also well suited but only available for VMware ESX.

Where does that leave us if we run VMware Workstation and want to back up the entire VM Guest ‘image’? The easiest manual way of doing this is by selecting ‘suspend’ in VMware Workstation for the relevant guest, copy the VM files to another drive  (.vmx, .vmdk etc) and restart the VM. Easy. The con of this, of course, is that you are bringing down your VM for a short period but for most situations it does the job and has the advantage of being very easy to get the VM started again in a DR situation (i.e. simply take the backed up VM files and fire them up on a new host).

So how can we use the above ‘suspend and resume’ approach but automate it so it does this nightly for example? The command line comes to your rescue :-)

Before you start, ensure your relevant VM is not open on the desktop through the VMWare Workstation GUI, as this locks the instance.

Starting a VM instance from the command line

Use vmrun command with start parameter, for example:

“C:\Program Files\VMware\VMware Workstation\vmrun” start “F:\YourVMs\YourVMInstance\Windows Server 2003 Standard Edition.vmx”

Suspending a VM instance from the command line

Use vmrun command with suspend parameter, for example:

“C:\Program Files\VMware\VMware Workstation\vmrun” suspend “F:\YourVMs\YourVMInstance\Windows Server 2003 Standard Edition.vmx”

(for more switches, simply type vmrun with no parameters).

Creating a backup script

We’ve now got all we need to create a very simple windows cmd file, for example, create a VMBackup.cmd file and enter:

:: Suspend VM
“C:\Program Files\VMware\VMware Workstation\vmrun” suspend “F:\YourVMs\YourVMInstance\Windows Server 2003 Standard Edition.vmx”

:: ROBOCOPY (or xcopy) the files somewhere, pref a different box or network drive, for example
Robocopy.exe F:\YourVMs\YourVMInstance\ G:\externaldrive\backuparea\VMBackups\ /e /np /eta /r:1 /w:1 /log:F:\VMScheduledBackups\Logs\YourVMInstance-BackupLog.txt

:: restart the VM
“C:\Program Files\VMware\VMware Workstation\vmrun” start “F:\YourVMs\YourVMInstance\Windows Server 2003 Standard Edition.vmx”

You can now schedule this cmd file to run as a windows scheduled task in the normal way.  I use both a daily and weekly backup regime.

This is obviously a very simple backup approach and it does not alert backup failures etc (you’ve just got your trusty logs!) but for non-critical sandbox type environments it does the job nicely.

5 comments:

  1. if you want to extend this to include some log, it should be possible to add a line or two to your script to echo comments and run a md5 or similar on the source and backup.
    i am not about to add how to do this but a quick search of the internet should be enough to work it out

    Reply

  2. Yes absolutely, all my backup scripts have full logging, but these were not included to keep the example short. Cheers Dave

    Reply

  3. Hi – I completely powered off my VM and copied the entire VM directory. I then used the “Import” feature to add the VM to a different computer also running VMWare Workstation 7. The VM began booting, but blue-screened during Windows start-up. Can you see anything I did wrong?
    I am running VMWare Workstation 7 and my guest OS is Windows Server 2008 R2 Standard.

    Reply

  4. I’ve not had any problems opening backed up VMs (we have DR tested a few times), although we have not upgraded to Workstation 7 yet. It is worth reading the release notes for version 7 http://www.vmware.com/support/ws7/doc/releasenotes_ws7.html to see if this helps you. Dave

    Reply

  5. Its worth noting that you can save your commands as “jobs” in RoboCopy, which allows for really short commands to backup and restore on the fly if you can’t schedule this to be done regularly (like on my company laptop I take home every night).

    The /SAVE:jobname command will save the preceding command as a .rcj file, you can then run robocopy /JOB:jobname.rcj and it will execute the commands saved in the file.

    Reply

Thomas Challenger Thomas Challenger