Getting Yahoo! Finance data into Excel

Here’s a little VBA snippet that allows to retrieve Yahoo! Finance data into an Excel spreadsheet.


=yfQuote(ticker, date, field), where

  • ticker (required) is ticker symbol used by Yahoo! Finance. Examples: MSFT, ^GSPC, VXX.
  • date (optional) is the date for which quotes are sought. If omitted, the most recent available historical quote is retrieved. Example: DATE(2014, 10, 11).
  • field (optional) is the data field requested. If omitted, close price is returned. Allowed values: 1 (open price), 2 (high price), 3 (low price), 4 (сlose price), 5 (volume), and 6 (adjusted close price).
Public Function yfQuote(strTicker As String, _
    Optional dtDate As Variant, _
    Optional intField As Variant)

    ' dtDate is optional. If omitted, use today. 
    ' If value is not a date, throw an error.

    If IsMissing(dtDate) Then
        dtDate = Date
        If Not (IsDate(dtDate)) Then
            yfQuote = CVErr(xlErrNum)
        End If
    End If
    ' intField is optional. 
    ' If omitted, use 4 to retrieve closing price.

    If IsMissing(intField) Then
        intField = 4
        If Not (IsNumeric(intField)) _
               Or (intField > 6) _ 
               Or (intField < 0) Then
            yfQuote = CVErr(xlErrNum)
        End If
    End If
    Dim dtStartDate As Date
    Dim strURL As String 
    Dim strCSV As String 
    Dim strRows() As String 
    Dim strColumns() As String
    Dim dblResult As Double
    dtStartDate = dtDate - 7
    ' Compose the request URL with start date and end date

    strURL = "" & _ 
      strTicker & _
      "&a=" & Month(dtStartDate) - 1 & _
      "&b=" & Day(dtStartDate) & _
      "&c=" & Year(dtStartDate) & _
      "&d=" & Month(dtDate) - 1 & _
      "&e=" & Day(dtDate) & _
      "&f=" & Year(dtDate) & _
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    objHTTP.Open "GET", strURL, False
    strCSV = objHTTP.responseText
    ' The most recent price information is in the second row; 
    ' the first row is the table headings.
    ' Order of fields:
    ' 0 -- Date
    ' 1 -- Open
    ' 2 -- High
    ' 3 -- Low
    ' 4 -- Close
    ' 5 -- Volume
    ' 6 -- Adj Close

    ' split the CSV into rows
    strRows() = Split(strCSV, Chr(10)) 

    ' split the most recent row into columns
    strColumns = Split(strRows(1), ",") 

    dblResult = strColumns(intField)
    yfQuote = dblResult
    Set objHTTP = Nothing

End Function


  1. Yahoo! Finance doesn’t allow retrieval of historical prices for currencies, so this function wouldn’t work with currency tickers such as USDEUR=X.
  2. With some additional fiddling, the function could be persuaded to accept 0 to return the date for which quotes are retrieved. This could be useful when retrieving the most recent quote. As is, this wouldn’t work because of dblResult = strColumns(intField) (double value is expected, but a string is returned; there would have to be a conversion of that string into a date value)…
Posted in Finance and Investments, Technology | Leave a comment

Size doesn’t matter; age does

John Haltiwanger, Ron S. Jarmin, and Javier Miranda, “Who Creates Jobs? Small versus Large versus Young”, The Review of Economics and Statistics, May 2013, Vol. 95, No. 2, Pages 347-361.


The view that small businesses create the most jobs remains appealing to policymakers and small business advocates. Using data from the Census Bureau’s Business Dynamics Statistics and Longitudinal Business Database, we explore the many issues at the core of this ongoing debate. We find that the relationship between firm size and employment growth is sensitive to these issues. However, our main finding is that once we control for firm age, there is no systematic relationship between firm size and growth. Our findings highlight the important role of business start-ups and young businesses in U.S. job creation.

The entire article is here.

Posted in Business, Clippings, Economics | Leave a comment

The top 15 cities for tech startups

From Infoworld:

The National Venture Capital Association (NVCA) list measures overall venture capital investment in each respective market to compile its data. Continue reading

Posted in Business, Clippings, Technology | Leave a comment

NoSQL Taxonomy

Examples in parentheses:

  • Key-value store (Amazon DynamoDB)
  • Column-family store (Cassandra)
  • Document database (MongoDB)
  • Graph database (Neo4j)
Posted in Technology | Leave a comment

Estimated Costs of Crop Production in Iowa

Dr. Michael Duffy of Iowa State University publishes them online.  Next time I attempt working out the economics of alternative fuels, I should start there…

Posted in Business, Economics | Leave a comment

Digital FX solo

German animator Kaleb Lechowski recently released a short, but very well made, computer-generated sci-fi film.  According to the author, the six-and-a-half-minute film took seven months to make, including two months spent on writing the script.  The entire film crew consisted of three people: voice actor David Masterson, sound engineer Hartmut Zelle, and Kaleb himself, who did everything else.

It appears that the mythical man-month just took on a new meaning: one minute of computer-generated screen time…

Posted in Technology | Leave a comment

Disabling Java

From Infoworld:

Step 1: Find out which version of Java you’re running. The easy way to do this is through the Java Control Panel — if you can find it. Start by bringing up the Windows Control Panel (in Windows XP and Windows 7, choose Start, Control Panel; in Windows 8, right-click in the lower-left corner of the screen and choose Control Panel). If you see a Java icon, click on it. If you don’t see a Java icon (or link), in the upper-right corner, type Java. If you then see a Java icon, click on it.

Unfortunately, there’s a bug in at least one of the recent Java installers that keeps the Java icon from being displayed inside Windows Control Panel. If you can’t find the Java icon, go to C:\Program Files (x86)\Java\jre7\bin or C:\Program Files\Java\jre7\bin and double-click on the file called javacpl.exe. One way or another, you should now see the Java Control Panel.

Step 2: Make sure you have Java Version 7 Update 11. In the Java Control Panel, under About, click the About button. The About Java dialog shows you the version number; if you’ve patched Java in the past few months, it’s likely Version 7 Update 9, 10, or 11. (Don’t be surprised if Java says that it’s set to update automatically, but doesn’t. I’ve seen that on several of my machines.) If you don’t have Java 7 Update 11, go to Java’s download site, and install the latest update. You have to restart your browser for the new Java version to kick in. Personally, I also reboot Windows.

Warning: Oracle, bless its pointed little pointy thingies, frequently tries to install additional garbage on your machine when you use its update site. Watch what you click.

Step 3: Decide if you want to turn off Java in all of your browsers. That’s certainly the safest choice, but some people have to use Java in their browsers from time to time. Personally, I don’t disable Java in all of my browsers (more about that in a moment).

Step 4: To turn off the Java Runtime in all of your browsers, from inside the Java Control Panel, click or tap on the Security tab, then deselect the box marked Enable Java Content in the Browser. Click or tap OK, and restart your browsers (or better yet, reboot). From that point on, the Java Runtime should be disabled in all of your browsers, all of the time. To bring Java back, repeat the steps and select the box marked Enable Java Content in the Browser (the setting should, in fact, say “Enable Java Content in All of Your Browsers”).

Step 5: If you don’t want to turn off Java in all of your browsers, choose the one browser you wish to leave Java-enabled. For me, that’s an easy choice: By default, recent versions of Chrome prompt before running Java on a specific page, so I turn off Java in all of my browsers except Chrome. That way I can use any of my browsers for general Internet work without fear of getting Javanicked. If I absolutely have to go to a website that requires Java, I’ll fire up Chrome specifically for that purpose.

Step 6: If you haven’t turned off Java in all of your browsers, turn off Java in each of your selected Java-free browsers. In Internet Explorer 9 or 10, click on the gear icon in the upper-right corner and choose Manage Add-Ons. Scroll down to the bottom, under Oracle America, Inc., select each of the entries in turn; they’ll probably say “Java(tm) Plug-In SSV Helper” or some such. In the lower-right corner click the button marked Disable. Restart IE. At the bottom of the screen, you’ll see a notice that says, “The ‘Java(tm) Plug-In SSV Helper’ add-on from ‘Oracle America, Inc.’ is ready to use.” Click Don’t Enable. If you get a second notice about a Java add-on, click Don’t Enable on it, too. That should permanently disable Java Runtime in IE.

In any recent version of Firefox, click the Firefox tab in the upper-left corner and choose Add-Ons. You should see an add-on for Java(TM) Platform SE 7 U11. Click once on the entry, and click Disable. Restart Firefox.

In Chrome, type chrome://plugins in the address bar and push Enter. You should see an entry that says something like “Java (2 files) – Version:” Click on that entry and click the link that says Disable. Restart Chrome.

Step 7: Test. Make sure the browsers are/aren’t running Java, according to your wishes, by running each of them up against the Java test site. If you go to that site using Google Chrome, there better be a big yellow band at the top of your screen asking permission to run Java just this once.

Selectively disabling Java in your browsers isn’t particularly easy, but it’s a worthwhile step that everyone — absolutely everyone — should undertake. Right now.

Posted in Technology | Leave a comment

Technology vs. economy in a nutshell

Paul Krugman continues to ponder the impact of technology on income distribution.  The takeaway, in my opinion, is here:

Smart machines may make higher GDP possible, but also reduce the demand for people — including smart people. So we could be looking at a society that grows ever richer, but in which all the gains in wealth accrue to whoever owns the robots.

A troubling prospect indeed…

Posted in Economics, Technology | Leave a comment

I was wondering about this for a long time…

…and, apparently, so did Paul Krugman:

Robots mean that labor costs don’t matter much, so you might as well locate in advanced countries with large markets and good infrastructure (which may soon not include us, but that’s another issue). On the other hand, it’s not good news for workers!

This is an old concern in economics; it’s “capital-biased technological change”, which tends to shift the distribution of income away from workers to the owners of capital.

Twenty years ago, when I was writing about globalization and inequality, capital bias didn’t look like a big issue; the major changes in income distribution had been among workers (when you include hedge fund managers and CEOs among the workers), rather than between labor and capital. So the academic literature focused almost exclusively on “skill bias”, supposedly explaining the rising college premium.

But the college premium hasn’t risen for a while. What has happened, on the other hand, is a notable shift in income away from labor:

If this is the wave of the future, it makes nonsense of just about all the conventional wisdom on reducing inequality. Better education won’t do much to reduce inequality if the big rewards simply go to those with the most assets. Creating an “opportunity society”, or whatever it is the likes of Paul Ryan etc. are selling this week, won’t do much if the most important asset you can have in life is, well, lots of assets inherited from your parents. And so on.

I think our eyes have been averted from the capital/labor dimension of inequality, for several reasons. It didn’t seem crucial back in the 1990s, and not enough people (me included!) have looked up to notice that things have changed. It has echoes of old-fashioned Marxism — which shouldn’t be a reason to ignore facts, but too often is. And it has really uncomfortable implications.

But I think we’d better start paying attention to those implications.

Indeed.  And the case for some sort of redistributionist policy is getting stronger…

Posted in Economics, Technology | Leave a comment

Major IT wizardry…

Mina Naguib is chasing down a weird network malfunction:

At AdGear Technologies Inc. where I work, ssh is king. We use it for management, monitoring, deployments, log file harvesting, even real-time event streaming. It’s solid, reliable, has all the predictability of a native unix tool, and just works.

Until one day, random cron emails started flowing about it not working.

Move over Jonathan Kellerman…

Posted in Technology | Leave a comment