Pulling Company Financial Statements from Yahoo Finance

Here's an interesting $50 Project I just completed. The request was for a spreadsheet that would allow the user to enter a stock ticker symbol, and it would go out to the Yahoo Finance website and pull back either the quarterly or annual versions of the income statement, balance sheet, or statement of cash flows for the company. I think I've accomplished that pretty well.

I even did some formatting to make them look similar to those on Yahoo Finance. Here's what the Yahoo Finance version of the Cash Flow statement looks like...


(click on the image above to see a larger version)

And here's the Excel version ...


(click on the image above to see a larger version)

I even did a little "...gathering data...one moment please..." status message while the query is running.


(click on the image above to see a larger version)

This is another example of a web query project that I did where instead of using the CreateObject("MSXML2.ServerXMLHTTP") method, I actually initiate an Internet Explorer session behind the scenes using the CreateObject("InternetExplorer.Application") method. Using this method was preferable for this project, as I was able to identify the table containing the financial statements and pulled the "inner HTML" from it rather than having to use Regular Expressions to tease out each data point.

I thought it was interesting, but being the Excel_Geek, I probably would.

Excel_Geek Insiders subscribers, your file is on its way!

Later,

Excel_Geek

The Amazing Chase is Back!

Hey everyone,

It's that time of year again -- time for the Amazing Chase. This is a super-fun fundraiser, modeled after the television series The Amazing Race, put on by St. Monica's Behvioral Health Services for Women. My team, "Scared Hitless" actually won this last year. Last year we raised over $2,500, which started us in 5th, but we were able to make it up. The competition looks fierce this year, though, so we're gunning for $4,000.

The first leg of the race involves raising money for St. Monica's. The more we raise, the better starting time we get. Here's where you come in. Here's a quick, easy way to support my team. Any little bit you can afford helps. AND, for one lucky donor, I'll throw in a free $50 Project.

Thanks in advance to everyone who helps.

Later,

Excel_Geek

Parsing Google Search Results for URLs

I had a visitor on the blog yesterday who was interested in my old Google search results parsing code. Apparently he found Goohackle's Gooparser and liked what it does -- i.e. return just a clean list of URLs from the search results of Google -- but wanted a way around the limit for the number of results and a way to pull them directly into Excel.

Voila!

I cleaned up the old code for him. The result is a nice, clean worksheet that returns just the URLs from Google search results pages. You can set how many results per page and pages you'd like to return. Be careful, though, as if you hit Google too many times too quickly, they'll start to block you, asking you for your API key, etc. I've built in a random pause (3 to 7 seconds) between the pages in a multi-page query in an effort to prevent this from happening, but no guarantees. Google is a pretty sophisticated outfit, so they may still be able to detect an automated harvesting of data without using the API.

Geoff, enjoy the file. Insiders, the file's on the way.

Later,

Excel_Geek

Updated UPC Barcode Generator

Remember back when I told you about a $50 Project where I created a handy little UPC barcode generator in Excel? I noticed after that post I receive a meaningful amount of traffic from search engines based on phrases like "barcode generator" and the like. I kept wondering who those people were. Well, not that long ago I found out who at least one of them is: a guy named Ian.

Ian contacted me and wanted me to tweak the barcode generator I had built to suit his needs. Instead of generating one UPC barcode image at a time that he could copy-paste as he needs, he wanted to be able to generate long lists of UPC barcode images and save them as .JPEG files, so that he could later use a mail merge type function to pull them into documents as he needed. Sweet idea. Seemed pretty straightforward...

Turns out that getting those images I created using the little known camera feature in Excel exported to .JPEG files is not so simple. What I ended up doing is creating on the fly what is essentially a blank chart object and them filling it with the image, then exporting the chart as an image using the Chart.Export function in VBA. Whew. Here's a screen shot:



(Click the image to see a larger version.)

For a long list (hundreds or thousands of UPC codes) the process can take some time to complete, so I also threw in a little feature I've been doing when run times last more than a few seconds -- a little progress bar screen. It's essentially another normally hidden tab in the file that appears during run time to show the user how far along they are. Looks cool, too, I don't mind saying:



(Click the image to see a larger version.)

Ian, hope the file's everything you were hoping for.

Excel_Geek Insiders, your copy of the file's on it's way.

Later,

Excel_Geek

Testing the new Twitterfeed.

Oooooo...I'm gettin' all fancy now, I guess. Just another venue for my followers to received updates. Also, I think I might have a solution, now, for a past client looking for a way to regularly update his followers via email. Here's what I mean.

Later,

Excel_Geek

2008 March Madness NCAA Basketball Bracket

Selection Sunday is over. I've updated my handy little bracket spreadsheet with the selected teams. Those who'd like to download and use it, here's the drill:

1. Click here to send me $3.00 via PayPal.
2. Once you've made the payment, you should land back on a page to download the file. If for some reason that doesn't work properly, click here to download the file.
3. Open the file, making sure you can enable the macros -- that is, your Macro security is set to Medium at highest (Tools --> Macros --> Macro Security). Take note of the "lock code" it gives you.
4. Email me your lock code, and I'll send you back an associated "key code" that unlocks the file for your use. This code is specific to you and the computer you are using. If you want to use the file on numerous computers, let me know, and I'll see if I can accommodate you.

Good luck with your picks!

Excel_Geek Insiders, if you'd like to use the file, simply download it, and email me your "lock code", I'll send you the "key code" as part of your subscription.

Later,

Excel_Geek

Prepopulating a Userform

Just recently I banged out a quick little example for a reader who wanted to know how to quickly and easily store values from a userform created using VBA, and then prepopulate the userform the next time it's brought up with those stored values. Basically, I created a new worksheet in the file that would remain hidden and had a named ranged in that worksheet for each field in the userform. Then, when the userform was called up, I grabbed the values from those named ranges and put them in the inputs in the form. Then, when the userform was submitted, I changed the values in those named ranges to those currently in the userform. Pretty slick.


Like so many things Excel+VBA, it's easy when you know how. I thought my Excel_Geek Insiders would appreciate this simple example.

Later,

Excel_Geek

(how about that, three posts in one day after two months with nothing...can you tell I've been busy?)