Tuesday, December 27, 2005

UPDATE on Google API Project

I fired off an email to S_ _ _ _, trying to get some clarification, and with an update. S_ _ _ _ shot back an answer, showing that he/she's already got quite a bit of insight on this project. Further proof that my readers are, in fact, some of the brightest excel users already...they're just looking to me to help them tweak the masterpieces they've already built! (Hope I can live up to that.) Anyway, I thought all of you might find the subject matter interesting, so here're the emails:

Excel_Geek to S_ _ _ _ :

I have a question and an update:

Question: When you say, "# of competing websites for the search term" do you mean the total number of search results for a given search term, or do you mean the number of Google Adwords client advertisers who have purhcased that particular search term? Obviously the two are vastly different, and getting the latter, if even possible, would require me to be an Adwords client (which I'm not) so I could get access to the Adwords API. What I don't know is if I did have access to the Adwords API if it gives out that sort of information.

Update: Backlinks? Totally doable with the Google Search API. URL? Totally doable with the Google Search API (even without it). PageRank? Hmmmm.....This is what seemingly everyone is trying to achieve (so I've found). Google doesn't make this data readily available via API, and they stopped placing PageRank in their search results pages long ago, but there is the Google Toolbar, which displays the PageRank. I found one application in DOS that returns the PageRank, but it isn't useful to me. I have some ideas on how we might get this data via the toolbar....I'll keep you posted.

Excel_Geek


S_ _ _ _ to Excel_Geek :

Hi,

Answer to the question : I mean the total number of websites for the search term, which google displays in the SERP top left corner. (such as Results 1 - 10 of about 1,720,000,000 for web sites. (0.23 seconds) )

Side Info : I already have a program called keyword analyzer (http://www.keywordsanalyzer.com) which can fetch the number of google campaigns and top bids (exact, broad and phrase match options seperately). I guess this also handled with SOAP, similar to what you have done.

PageRank : There are a few sites which can fetch the pagerank (in text or in graphics) for a given keyword, most of them use ASP or PHP. Here are a few references. It might help.

http://www.thinkbling.com/serps/serps.php
http://www.mcdar.net/KeywordTool/keywordtool.asp
http://www.webuildpages.com/cool-seo-tool/
http://www.splutweb.com/Tools/Tools.asp

Interesting New Google API Related $50 Project

Just before Christmas (Merry late Christmas, by the way...) I received a request for an interesting new Google API-related $50 project.

S_ _ _ _ requested the following data be returned into an excel spreadsheet for each of a list of up to 300 or so keyword phrases:

  • The number of websites competing for the search term keywords, as well as intitle:keywords and allinachor:keywords;

  • The number of backlinks of the 1st, 3rd, 5th, and 10th ranked competing sites for the search term keywords;

  • The pageranks of the 1st, 3rd, 5th, and 10th ranked competing sites for the search term keywords;

  • The URLs of the 1st, 3rd, 5th, and 10th ranked competing sites for the search term keywords;


And some bonus optional data S_ _ _ _ would like are the number of searches in Overture Database and the number of searches in Wordtracker Database (using the WordTracker API, with which I'm not terribly familiar).

I'll get right on it, S_ _ _ _, and I'll see what I can do.

Excel_Geek

Wednesday, December 21, 2005

Google Search Results Page Parsing Code BIG in Russia?

Here's an interesting little tidbit: That Google search page parsing code I posted about way back when...it apparently was a hot topic in Russia! Wow.

Anatoliy Alizar wrote an article on www.webplanet.ru in which he said this about me...

"Generally, the amateurs of program Excel already wrote considerable code for processing of results of Google. Recently one of them published the special the parser, which is processed HTML- page with the results of search Google imports list URL into the cells of sheet Excel. This parser is completely suitable as the basic element for the set of useful macros."

"...amateurs of program Excel..." Indeed, I've been revealed as a fraud! Ha! Seriously, though, I'll humbly confess that "amateur" is likely closer than "expert."

Basically, Anatoliy is holding me out as an example of stuff programmers can do with Google even without subscribing to and using their available APIs. Fair enough... His article has been picked up by many other Russian blogs and websites, so he must have some credibility on this sort of subject matter.

After realizing (through Google Analytics) that considerable traffic was flowing from Russia (man, I love their geo targeting fatures!), I found a nice little website/text translator that handles Russian: www.worldlingo.com. Babel Fish, which I've used loyally for years, claims to do the same, but it errored out on me every time I tried to translate Russian websites to English.

So, to my new-found Russian readers, "Добро пожаловать к Excel_Geek."

взгляд на русском языке.

Sunday, December 18, 2005

Limitation on MSN's Stock Quote toolbar add-in

Well, it's happened: I have received a project that I will not be able to produce as specified.

Tom wanted a way to update a system of Excel worksheets using MSN's Stock Quote toolbar add-in to refresh current stock quote data. He uses this system to track and produce billing information for his investment management clients' accounts. The problem with the add-in is that it only allows users to get updates every five minutes. I cannot find any way around this limitation on refreshing stock quote data. I've tried everything I can think of: taking over refreshing with VBA commands, using different "instances" of Excel (as opposed to opening multiple files in the same instance of Excel), etc.

In addition, none of the other Excel gurus out there have published anything (that I can find) on the topic about getting around this limitation. Most of them actually balk at the idea of using the toolbar at all, "...when getting data via web querying Yahoo is available..."

In the end, I've had to suggest to Tom two alternatives, neither of which, I'm sure, are all that attractive, given the amount of time, effort, and familiarity he's got with his system as designed now:

  1. Redesign the system so that there's one "master" Excel file that contains all current quote data for all client-account-holdings, and then have each of the account worksheets reference data from that file. (This creates only one place that has to be refreshed, so doing one master refresh will ripple down to each of the other worksheets when you click "update links" upon opening them.)

  2. Redesign each of the client-account worksheets incorporating automated web queries (using VBA) to enable automatic and ad hoc/on the fly "refreshes" as often as needed.


We'll see what he wants to do. I, of course, won't be asking for $50 until he's satisfied that he's gotten a solution he can live with.

Aany readers out there that have experience with the MSN Stock Quotes toolbar add-in, and have some suggestions for getting around this 5-minute limitation, please send them to me or post a comment on this blog. Thanks in advance!

Excel_Geek

Friday, December 16, 2005

Interesting New $50 Project - Excel as Tool for Investment Management

Tom from New Jersey recently requested a project involving the system of Excel spreadsheets that he uses to track and manage his investor clients' portfolios, as well as provide billing for his services.

He's using Smart Tags to retrieve current stock quote information (from MSN Money Central), but is having some trouble. MSN Money Central only lets him refresh the quotes every five minutes (per spreadsheet). This is somewhat problematic because if a single spreadsheet has more than one worksheet (for example, if a client has more than one IRA account, he might have them each in a different worksheet within the same spreadsheet) he has to wait five minutes between refreshing each client account.

Tom would like to know if there's some "master switch" he can throw to get all stocks in every worksheet updated, even if it's once per day, say at 6 pm or 9 am, or anytime. Further, he's looking for some kind of macro that would automatically open all of these files and refresh all stock quote info at a particular time. Further, he's spent a great deal of time developing his system and has around 200 of these worksheets, so any wholesale change -- like switching the source of his data from MSN Money, etc. -- would be very problematic.

This is going to be a fun one. I love investment-related projects. Makes me want to get my old day trading automator back out and dust it off...memories...

I'll keep you all posted.

BTW, my Excel_Geek Blog_Mail subscriber list keeps growing! I want to, again, pass along my humblest appreciation to all of you who make me feel as though what I'm doing really helps people. Thank you.

Sunday, December 11, 2005

Write Your Own Excel E-Book?

Charley Kyd recently posted a new article on ExcelUser.com about writing your own Excel-related e-book. Interesting...

Maybe I should write my own e-book on Google "hacks" using Excel and VBA. Maybe an e-book on building your own direct email marketing system using Excel, Outlook, Outlook Remption, and VBA...

Charley's article lays out a nice example of how writing an e-book might stack up against writing a traditional book, financially, and she talks about her most recent e-book on Excel Dashboard Reporting. She doesn't say exactly how much she's made with that book, but I can tell you this much: I've been an affiliate marketer of her book on dashboard reporting for a few months, and I've helped her quite a few already...on my tiny little blog!

I'm going to mull around this idea of writing an e-book for awhile, and I'll keep you all posted.

P.S. I want to pass along my sincerest thanks to all of my loyal readers, including the growing list of Excel_Geek Blog_Mail service subscribers. It's you all that make thoughts of writing an e-book on Excel even within the realm of reality.

Thank you,

Excel_Geek

Tuesday, December 06, 2005

Micropayments Review #1

We're two weeks into the PublicWishingWell.com experiment, so I thought I'd pass along some of our findings thus far with respect to PayPal's micropayment services:

First, let me just say that frustration would be understating our initial feelings. Understand that PayPal released its first press release regarding micropayments on August 31st of this year, so we figured the service was up and running in full, complete with documentation, well-trained support (as we've experienced with PayPal's traditional merchant services). We were, however, dead wrong.

When seeking information on the topic of signing up for a micropayments account, the only means publicly available was the email alias micropayments@paypal.com, which was very slow to respond. In fact, I used other means to actually sign up for the account before ever hearing back from that email alias. Because I had personally been in contact with a business development agent (Jennifer, out of Omaha) for other merchant services needs, I simply called her back and she sent me a promotional email with a link to sign up for a micropayments, enabled account.

The URL was a redirect that landed me back on what seemed to be the standard "create an account" first page, but I was assured it would create a mircopayments enabled account. Even after creating and using the account, however, which did charge the appropriate $0.05 + 5% fees, without actually looking at the fees charged on actual receipts, you'd never know it was a micropayments account. It's not stated anywhere, there's no special micropayments-specific instructions or documentation, and, in fact, when you click on the "transaction fees" link while logged in, you'll actually be told that the account is enable for "standard fees". Confusing.

Oh, forgot to mention, instead of simply having micropayments added to your existing business or premier account you have to get another one for micropayments, which, many of you may know, flies in the face of PayPal's own stated policy of "one personal, one business/premier account per person." When I called customer service about both the issues of not being sure whether or not my new account was, in fact, a micropayments account, and if I was violating their own terms by having more than one business/premier account, despite needing a new one to have micropayments, half of the support people I talked to tried to convince me that PayPal did not, in fact, have any such thing as micropayments, and the other half had heard of it, but had no idea.

Long story short, Jennifer, my angel, was sick for a few days, so I couldn't get in touch with her, and I grew increasingly frustrated, eventually blasting off a steaming email to Peter Ashley, the stated manager of digital content and micropayments for PayPal. Jennifer got back, returned my messages, reassured me that I was set up right, and I was on my way, albeit with no documentation. I'm complaining a bit about documentation, but in the vast majority of aspects, micropayment deployment is identical to standard PayPal services. Don't be afraid.

A great bright spot (to me), is that Mr. Peter Ashley called me back after receiving my email. I happened to be on the phone at the time, so he left a voicemail for me. We're still trying to get hooked up on a call to hash through some of this, but the basics of his message were this: He understands and agrees with the issues I've brought up (lack of documenation, trained support personnel, and apparent need to violate PayPal's own terms to use micropayments if you already have a business/premier account), and he'd like to have the chance to explain why this is. He breifly stated in his message that the micropayments "initiative" was a "soft launch" (like alpha or beta in my estimation) whereby PayPal would be set up to handle inquiries from companies like ours who have a need. They'd learn from early users what exactly is needed, how micropayments will be used, etc., then do some additional development on the services and "get ready for a prime time launch." I'll post my findings, if interesting, I guess, after actually speaking with Mr. Ashley.

Review #1 Conclusion: I was intensely frustrated by PayPal's "soft launch" approach, but now understand it. PayPal's micropayments (when you're assured you've actually got it) is every bit as easy as their standard services, and at $.05 + 5%, you can feasibly sell digital goods/services as cheap as $0.10 and still not lose money (you'd actually keep 40% or $0.04).

Wednesday, November 23, 2005

An experiment in micropayments - PublicWishingWell.com

Hey all,

Thought I'd share the news of the recent launch of a little experiment I'm involved with: PublicWishingWell.com.

It's an experiment on two fronts:

  1. Socio-Economic: People are willing to toss a quarter into a physical wishing well all the time -- check any mall, etc. Are they just as willing to "drop a quarter" into a virtual wishing well? What if everyone who visited the wishing well could see the wishes of others? Would that affect what people wish for?

  2. Technological: Micropayments are supposed to be the new currency -- on and off the Internet, enabling the sale of digital music, video games, sodas, and candybars. We've implemented PayPal®'s new micropayments service to see if it's all it's cracked up to be.

If you've got a chance, stop by the well, take a look around, drop in a quarter, and wish well™.

Tuesday, November 22, 2005

Google Analytics Update

Hey all,

Thought I'd drop a post today to give an update on my recent implementation (copy-pasting a few lines of HTML into my template is "implementation" right?) of Google's Analytics service. Here's what I've found:

Google was (apparently) ill-prepared for the swarm of customers they'd receive, as I believe they've now shut off new applications for Analytics, and the time frames they specify it should take to begin receiving data were about 1/8 of the actual time (12 hours vs. 4 days, which is how long it took me to begin receiving data consistently.

That said, the tool is wonderful. For those of you familiar with Urhcin, we've got pretty much the same thing here. I am particularly fond of the Geo Map Overlay feature (pictured below).



Whaddayaknow...the Excel_Geek is global!

All for now.

Excel_Geek

Monday, November 21, 2005

Excel Add-in Does Monte Carlo Simulations

Late last week I got a tip to check out a nice (relatively new) Excel add-in from RiskAmp that does Monte Carlo simulations.

You can check it out here: www.riskamp.com

Not terribly familiar with Monte Carlo? RiskAmp's got a nice white paper (.pdf) that gives a easy-to-understand overview.

I've downloaded and installed it (the add-in), and have played around a little bit. From what I can tell on the ultra-quick, it's pretty slick. I'll be doing much more testing, but I prsonally think this little bad boy will come in very handy for all of the pro-forma financial projections, and probabilistic modeling that I'll be doing in the very near future.

What's even better is the price point: For less than $60, you've got quick, easy computing functionality for which the "big boys" (investment pros, actuaries, etc.) have historically paid through the nose.

I say, check it out.

Friday, November 18, 2005

Excel 12 - Cool feature I've wanted forever...remove duplicates

I've been frantically reading the blog I posted for all of your to read yesterday (http://blogs.msdn.com/excel/), and while I first mentioned my excitement for really digging into the web services being rolled out, here's a wonderful feature that I've been begging them for forever: automated duplicate removal.

That's right. I've spent countless small chunks of my life adding temporary columns to data tables and entering formulas like...

=IF(B3=B2,"REMOVE","")

Then sorting the entire data table by that new column, and deleting all rows containing "REMOVE" in them.

No more. Check it out at http://blogs.msdn.com/excel/archive/2005/11/04/489259.aspx.

Getting excited for 12 yet?

Thursday, November 17, 2005

Microsoft Excel 12 - Check this blog...

More and more of you have been asking for information on Excel 12.

Well, a friend of mine (six-forty.net) tipped me off on this blog: http://blogs.msdn.com/excel/.

This blog features some indepth discussion of the new services to be included in 12. To be short: WOW. I cannot wait to get my hands on this. The web services stuff is awesome, and I am already thinking of the cool stuff I'll be able to build...

Wednesday, November 16, 2005

Great article by ExcelUser on VBA Standards for Excel Programmers

I was just reading another article by Charley Kyd over at ExcelUser (the guys with the sweet Excel Dashboard package for cheap!) on VBA standards for Excel "programmers." The article talked about providing documentation, and about using "Option Explicit".

While, I've been fairly meticulous about providng both "in-line documentation" (explanatory comments) and "title blocks" containing a general purpose statement for each macro, I especially appreciated the discussion on "Option Explicit" to remove the "variant by default" variable types. This is not something that I've routinely done, but will certainly endeavor to do from now on, as the benefits include enhanced processing performance and more easily identifiable errors.

While on the ExcelUser site, I also learned two other new things:

  1. For all of you stock-traders, you can get a free dashboard-style report for any company on the market.

  2. For all of you with your own Excel-related blogs or website, ExcelUser has a great Affiliate Marketing system. They'll pay you 30% on all sales you refer. I've signed up and have already referred 6 sales! Wow! Check it out.

Google's Free Analytics

Hey everyone, I just added Google's free new Analytics service to my blog, and guess what: It's free!

Be sure to check it out if you've got a website or blog.

Monday, November 14, 2005

Update on Email List Projects - PayPal® User Agreement

Everyone,

Remember those targeted email list projects that people have requested? Well, I recently learned that under the PayPal® User Agreement, I may not use PayPal's services to sell such services.

What does this mean? From this point on whenever anyone requests a targeted email list from me, as a $50 Project or otherwise, I'll be requiring payment in the form of a check, cashier's check, or money order. Note: I'll still be requiring people making such requests to attest to me that they'll not be using the list in violation of the CAN SPAM Act of 2003 or any other laws in their jurisdiction.

If you're interested, you can contact me for more details.

Later,

Excel_Geek

Friday, November 11, 2005

Apologies for the lack of posts.

I'd like to apologize for my relative lack of posts recently. My real job (running Internet start-up www.kolvensystems.com) has been all-consuming of late.

I'd like to ensure those who've submitted $50 Projects that aren't too time-sensitive that I'll do my best to get back to you on a timeframe for their completion.

More posts soon...

Monday, November 07, 2005

First Blog_Mail Subscriber - Secret Prize!

Hey, guess what? We've had our first subscriber to Excel_Geek's Blog_Mail service!

Thanks a bunch, Joseph. And now...here's your secret prize:

1 free $50 Project! That's right, think of something you need done in Excel -- something that would have cost you $50 -- and I'll do it for FREE!

Friday, October 21, 2005

Unbelievable! A sound synthesizer in Excel!

http://sunsite.univie.ac.at/Spreadsite/fourier/fourtone.htm

A friend just sent this to me. Wow.

Check the code behind it, too. I'm impressed.

Email list Project done

Done.

That was pretty simple and quick, and because the keywords used in the Google search were very specific to the intended target (in some cases even specifying the URL to search), I feel that the list was quite effectively targeted.

I pulled several hundred email addresses off in just a few minutes.

Anyone else?

Email address list request project - CAN-SPAM Act of 2003

Recently I was contacted by (anonymous as requested) with a request for a $50 Project under which I was to use my Google search results page parsing code in conjunction with my email address parsing code to compile a list of email addresses from web pages returned for Google searches of some very specific search keywords and terms provided by (anonymous).

I told (anonymous) that I would have to first research anti-SPAM legislation to be sure that in doing said project, I wouldn't be breaking the law. Specifically, somewhere I had been lead to believe that it may be illegal to harvest email addresses from website "automatically" and then send unsolicited emails to those addresses.

What I found is that the CAN-SPAM Act of 2003 is THE law of the land when it comes to unsolicited bulk email, and interpretation of the CAN-SPAM Act of 2003 is all over the board, depending upon which interest group (Advertisers or Anti-SPAM groups) is providing their interpretation.

Here's my (as) objective (as possible) take:

WHAT IT IS ILLEGAL FOR BULK EMAILER TO DO:

  1. Access a computer without authorization and intentionally initiate the transmission of multiple commercial electronic mail messages from or through such computer.

  2. Use a computer to relay or retransmit multiple commercial emails with the intent to deceive or mislead recipients or any ISP as to the origin of such emails

  3. Materially falsify header information in multiple commercial emails

  4. Register, using false registrant information, for five or more email or online user accounts or two or more domain names, and use such accounts to send multiple commercial emails.

  5. Falsely represent onself to the the registrant or the legitimate successor in interest to the registrant of five or more IP addresses and use such addresses to send multiple commercial emails.

  6. Send multiple commercial emails with false or misleading header information

  7. Send multiple commercial emails with deceptive or misleading subject lines.

  8. Send multiple commercial emails with no functioning return email address (and such address must remain capable of receiving message for 30 days after transmission of said emails)

  9. Send a commercial email message to a recipient who has asked to be removed from the list or not to receive any more messages.

  10. Send commercial email messages without including "identification" that the message is an advertisement, without an opt-out mechanism, and without the physical address and contact information of the sender.

Further, it will be considered an "aggravated offense" to break any of the rules in 6 - 10 when also doing the things in 1 - 5, or when having either 1) gathered the email addresses using automated means from websites, or 2) using a dictionary-type attack, generating possible email addresses by combining names, letters, or numbers into numerous permutations.

CONCLUSION:

Now, I am not an attorney by any stretch (and I will not be held liable for any damages relating from others' actions on the "advice" of my interpretation of the Act), but my interpretation of the CAN-SPAM Act of 2003, as it relates to the project requested by (anonymous), is that it would NOT be a violation of the Act on my part to provide a list of email addresses collected using "automated" means from websites to someone else provided I had reasonably been assured by that person that they would not be using those email addresses in such a way as to violate the Act, and (anonymous) would not be violating the Act provided that he/she did not do any of the things in 1 - 5 and that he/she did include all of the things required in 6 - 10.

I will perform this $50 Project and any similar others requested by people who give me an assurance that they will not use the addresses in violation of the CAN-SPAM Act of 2003.

Some free excel spreadsheet solutions for your business needs

Hey business owners out there, small and large, recently I was contacted by Afra AmirSanjari of Peacock Capital. Afra saw my blog and thought that perhaps my audience would be interested by or could use some of the Excel spreadsheet solutions that he's put together and offers free on his website. www.peacockcapital.com/free-excel-spreadsheets.htm

Examples of some of the spreadsheets, you ask? "Capital Budget Analysis" "Dividend Discount Model" "Leveraged Buyout Valuation" You get the idea.

Hey, if you find one or more of Afra's spreadsheets that would be great for your situation, only with some tweaks here and there, but you don't know how...let me know. $50 is all it'll cost you for a customization.

BTW, he's also got a fairly interesting blog. www.peacockcapital.com/blog

Check 'em out.

Tuesday, October 18, 2005

The full line of services from the Excel_Geek

I've been having a great time and have cranked out a great many Excel_Geek $50 Projects, and based upon the response that I've gotten from happy customers and from other visitors to the blog, I've decided to roll out three new services, so here, in a nutshell, are the (now) four services offered by the Excel_Geek:

  1. Excel_Geek $50 Projects - Price? $50 flat fee. An oldie, but a goodie...the original...the Excel_Geek $50 Project. Got a problem you think that could perhaps be solved using Excel? Send it to me and I'll solve it and send you the file containing the solution, complete with copious documentation and clarifying notes. Not everyone has the time, energy, or desire to post their Excel-related issues on a user forum, waiting, hoping, and praying that someone will not only answer the question correctly, but understandably. (Note: Excel_Geek retains all intellectual property and/or other rights to the files created under the Excel_Geek $50 Project program. Every attempt will be made to ensure that the privacy, confidentiality, and proprietary information of the requesters of Excel_Geek $50 Projects is in no way violated.) Why wait, hope, and pray when you can get your answer lickity-split from Excel_Geek for only $50. Come on...it's $50. Got $50?


  2. Excel_Geek Blog_Mail - Price? FREE. Subscribers to the Excel_Geek Blog_Mail service will have delivered to their email inbox each and every new post I make on http://excelgeek.blogspot.com. No need to visit the blog each day. No need to figure out what an RSS feed is or how to use it. Every time I've done something new or have something new to say, you'll get it in your email. Sign up today! It's FREE!

    Guess what? This is now a whole lot easier (for you and for me). I'm all set up on FeedBlitz now, so all you've got to do to get Excel_Geek in your email is subscribe using the form near the top of the left column on this page.


  3. Excel_Geek Insiders - Price? $39.95 per year. Subscribers to the Excel_Geek Insiders service will receive Excel_Geek Blog_Mail PLUS receive (as emailed attachments) the source files for all Excel_Geek $50 Projects completed throughout the year. That's right. You'll get for just $39.95 annually each and every file that the Excel_Geek created for someone else for $50 each. (Note: any and all private, confidential, and/or potentially proprietary information of the original requesters of the Excel_Geek $50 Projects will be removed from the files, replaced with suitable data where applicable.) Sign up today for Excel_Geek Insiders!


  4. Excel_Geek Private Projects - Price? Case by case bid at $50 $100 per hour. Like the idea of having the Excel_Geek take care of your Excel-related problems, but don't like the idea of the Excel_Geek sharing your solution -- even stripped of any private, confidential, or potentially proprietary information -- with Excel_Geek Insiders subscribers? Under the Private Project program, the Excel_Geek will do your projects privately and will not include the source files in the Excel_Geek Insiders program content. (Note: Excel_Geek retains all intellectual property and/or other rights to the files created under the Excel_Geek $50 Project program, but also issues customers under this program unlimited license to use, modify, transfer, and/or sell the files and/or source code, either as produced by Excel_Geek or modified, as customer sees fit. Excel_Geek warrants that the files produced under this program will not be made available to third parties without express written consent of the customer. Further, every attempt will be made to ensure that the privacy, confidentiality, and proprietary information of the customers of this program is in no way violated.) Feel better? Submit a Private Project request now!


Another web query-related $50 Project

James liked the last project so well, he requested another one right away. Thank you, James. For your reward for being a loyal customer, I placed your project at the front of the line (plus I was still in that web query mindset.)

For this project, he had many more different securities to lookup and return values for. I used Excel's built-in web query functionality, again, to do some of them, but also got a regular expression and the HTTP object to directly query some of the pages (ones on Yahoo Finance) directly, which is faster and just plain cooler.

James wanted the query to run every X minutes, which he wanted to be able to set himself. I was banging my head against the wall trying to figure out how to have one button launch a never-ending loop macro that paused for the set duration and have another button accessible to the user to stop the process. None of the methods used by others were working for me (perhaps due to the nature of the web querying going on). James settled for having one button to run the query once, and he'd click it again to run it again.

I believe I've figured out how to do his initial request, though. I was thinking about the problem wrong (as is the case in most/all such instances). Instead I needed a macro that would automatically run on file open, then stop completely for a set duraction (giving the user access to manipulate the file/run other macros), then run again, and repeat. I used the Application.OnTime method, and I think it's pretty slick. One thing, though, is that the time it takes to run all of the queries is included in the delay time, so plan accordingly.

Who else has $50?

Monday, October 17, 2005

New web query-related $50 Project

James (a much-appreciated repeat customer) requested a project on Friday that involved automating a weekly process of going to four different websites (one for each of four different mutual funds) and retrieving the effective 7-day yields for the funds and placing them in a newly added column in an Excel worksheet.

My initial plan was to use VBA to create web calls that returned the text for each of the four pages, then use four different regular expressions to match the data desired and return it, placing in the worksheet. However, I ran into a bit of trouble with the regular expression part of it, so I instead utilized the web query functionality built into Excel to retrieve the entire tables of data on each web page, then simply used cell referencing to place the data where it is to be stored in the worksheet.

In the end the results are identical, though I think performance suffered a bit. Total processing time (on broadband) takes about 5-6 seconds, while if I'd gotten my initial plan to work, it would likely have been 1-3 seconds.

I think I'll take another stab at doing those regular expressions (just for my own sake), and I'll share them with James if I make them work.

Thanks, James.

Everyone else, keep those $50 Project requests coming!

Friday, October 14, 2005

Getting more requests for Excel dashboard-type solutions

Everybody,

I have recently be getting quite a few requests for Excel "dashboard"-type solutions -- New York Jan, Washington (State) Stewart, West Virginia Doug, most recently. That is, what people seem to want are quick, easy one or two page reports that quickly condense down the most important metrics that concern them -- be they sales figures, units sold, inventory counts, daily trading volume, etc.

I'll keep cranking these out as fast as I can, but I do feel obliged to tell you that my good friend Charley Kyd's Excel Dashboard Kit is available for $0.05 less than my $50 fee, and it incudes her 150-page e-book, Dashboard Reporting With Excel, all the files created for the book, many example dashboard reports, two workbook databases, which contain actual performance information for Microsoft and Wal-Mart, and her special "Postage Stamp" report that can display 110 charts on one printed page, which uses a well-documented macro that populates the report page.

Now, I realize that what makes my $50 Project service attractive to many people is that they don't want to have to spend hours submitting questions to user forums, waiting and hoping for someone to answer their question, hoping the answer is correct, and trying to decipher the instructions, etc. They simply have a problem that needs solved and they want it solved now. If they learn how the solution was created, that's merely a bonus. (For those of you who haven't requested a $50 Project and received the solution, I do include meticulous documentation, explanation, and instructions in my solutions.)

Realizing this, if you simply want a dashboard-type solution created for you, or if you would like a starting point from which you can learn, I'll happily do it for you for $50, but just know that you can buy Charley's kit for $49.95, and have all you need to create and modify all the dashboard reports you'll ever need.

Now I'm off to crank out some $50 projects. Keep 'em comin'!

Friday, October 07, 2005

Just learned of this super nice online personal finance service from Mvelopes

Hey MS Money and Quicken users, like so many other software products out there, a new, web-based personal finance alternative is now available, and it blows away the desktop-chained relics.

Mvelopes Personal is an online budgeting system that makes it easy to create an effective personal budget and track every aspect of your spending as it happens. It will help you always know exactly how much you have left to spend, instantly know the impact of every spending decision, effectively manage credit card spending, and quickly create an easy to use household budgeting plan. Click here to learn more

Thursday, October 06, 2005

Adding a PayPal "Pay the Geek" button

For those of you who've asked for a quick, easy way to pay me for these $50 projects, I've added a PayPal "Pay the Geek" button.



I also strive to send PayPal invoices once you've told me that you're happy with what I produced for you, but this gives another means to pay, which is fine by me.

Update on Canada John's golf league project

I recently sent Canada John version 2 of his golf league score input system. Things added in were automatic point (individual and team) calculation based upon the league's rules, pars & handicaps of each hole, and handicap differentials of the two players pitted against each other.

I also added in a feature to automatically fill in the all-pars hole scores for the defualt "play against the course" player to reduce data entry.

Also, to reduce data entry, I added a schedule where John can specify on a week-by-week basis which teams are playing against each other and what they're starting holes are. Then, all John needs to do is set the week number and the starting hole, and the teams automatically fill in. Then he auto-populates each team and deletes moves players as needed, auto-fills in the "course" player (if needed), inputs the hole-by-hole scores, and submits the scores to be archived for use in calculcating individual and team points standings as well as maintaining up-to-date individual player handicaps based upon the gross scores.

I already have ideas for v 3.0: string together some macros to further stream line data entry -- e.g. "Submit scores + clear scores and player names + move to next hole + auto-populate teams" would save a few clicks. Also, I'd like to create the weekly summary page and overall standings pages that can be published to the web in the way John does is now.

All of this I'm doing in between the flow of other projects, many of which are too boring to mention...but they're still worth $50.

Who else has $50?

Monday, October 03, 2005

Quickie Project - Yahzee® scorecard for my wife

We ran out of Yahtzee® scorecards at home, so I whipped this one up real quick...



I stuck four of them on a single 8.5"x11" sheet, and while they're a touch smaller than the originals, they work just fine...

(Amber, you owe me $50.)

Thursday, September 29, 2005

New $50 Project - convert Word form into Excel

Thanks to John (from Texas) for his recent submission of a project.

John wants me to convert a business form that he had in Word into a form with automatically calculating fields in Excel. I busted it out pretty quick last night (and did a damn fine job, if I do say so myself, mirroring the layout, style, fonts, etc.) but have a few questions for John regarding the specifics of the calculations, etc., before I can complete it.

Man, it's been so long since Finance class for me...

The thing that is (still) boggling my mind is how to calculate the payment amount on a loan that has annual simple interest compounded monthly, if there are monthly (easy), annual (easy), quarterly (huh?), and semi-annual (huh?) payment plans. Anyone?

Anyway, I'm still trying to get back to do some fine-tuning for John (from Canada) on his golf league sheet, too...and the projects keep coming in. Don't think that I can't handle them, though. I'll get all the sleep I need when I'm dead!

Later,

Excle_Geek

Monday, September 26, 2005

I'm back from Mexico!

I made it back...barely.

Having to fly through Houston on Thursday when they were in the midst of canceling flight after flight was fun. We ended up jumping on standby on a flight to Oklahoma City since all of the Omaha flights had been canceled. (Anywhere north of here, you know!)

We made that flight then got on standby on a Southwest flight to Kansas City. Made that one, and rented a car in KC to drive home to Lincoln.

I'll dig through the emails for last week to see what all came in as far as $50 projects are concerned, and try to get them banged out tonight. I had zero access to the Internet in Mexico...which was at first scarey, then nice.

Time to get back in the swing of things.

Thursday, September 15, 2005

Oh, I forgot to tell you all that I'll be in Mexico for a week

Hey, I'm going to be in Mexico for about a week. I'll see you all again next Friday, so if you've got a project that is ultra urgent before then, I probably will have spotty access to the Internet from there, but I'll try.

If your needs aren't dire, keep firing away the $50 projects...right now I'm keeping up pretty good. I'm getting mostly same day turnaround, unless the projects are more involved and/or need more clarification on the submitter's end.

!Adios mis amigos!

Latest $50 project -- finding correlation between ad spending and revenues -- is done...

Edo recently submitted a request (Thanks, Edo!) for a means to analyze some advertising spending data and revenues over many periods, testing for correlation between each of multiple different advertising categories and the revenues.

Using =CORREL(), =HLOOKUP(), =MAX(), and =MIN() I set him up nicely with a solution that quickly and easily identifies top and bottom performers over the course of the entire time frame (cumulative for each period).

One should know that there's probably about a million other ways to go here.

Some other thoughts I had right off were to use the =RANK() function, both ranking ascending and descending, and then the =AVERAGE() function to average out the rankings over time to find consistent "winners" and "losers". Also, using simple conditional formatting, one could highlight ads that had particularly high (over a set parameter) or low (below a set parameter) correlations. And we haven't even left the use of the correlation statistic. There are many other statistics that return measures similar. If Edo was ambitious, maybe regression analysis is the way to go.

Regardless, here's another project done, and I didn't use a single line of VBA, but what the heck, it's another $50.

Wednesday, September 14, 2005

Just sent off John's golf league management project

John, thanks again for the project. I had a great time doing it, and I'll take a few more cracks and further integrating what I built with what you have, if you like.

To everyone else, what I basically accomplished for John was to create an easy, quick user interface to input the weekly results of a golf league. Using some fairly complex look-up referencing in Excel formulas, coupled with some VBA, I essentially turned one sheet of an excel workbook into a userform, and several others into various "tables". It's practically a database now!

Here's the interface:


What John gets is two tables -- one for individual historic performance, and one for team historic performance -- from which he can run all of his calculations to maintain handicaps, team and individual standings, as well as generate weekly results pages that he can export to the web.

Pretty slick if you ask me...and all for $50.

Tuesday, September 13, 2005

Update on the Excel Dashboard Kit - Quantity Discounts for companies

I just got a note from Charley Kyd over at ExcelUser, and they're rolling out a new quantity discount program, so that companies can get ahold of the kits for multiple users, and save substantially. Here's what she wrote:

"The Excel Dashboard Kit is licensed to a single user, not to an entire company. I recently received a request from a lady who wanted to purchase a quantity of kits. Here is the discount schedule I gave her:

Units Discount
2 = 20%
6 = 30%
10 = 40%
300 = 50%
500+ = 55%

These are approximiateldy the discounts that book publishers offer, except that they are slightly more generous for small quantities."

If your company is thinking about implementing a dashboard reporting system (or beefing up the one you have), be sure to check out Charley's prouducts.

People continue to take advantage of the $50 project deal

These keep coming in, and I'm steadily cranking them out...

A couple of the latest projects are from Brandy, who needed just a bit of help with some Excel Lab homework...

...and from John, who's looking to streamline the entry of golf scores into a golf league/handicap management spreadsheet that he uses to publish the results to a web page. Fun project, John, even though I'm not done yet. I was impressed by the publishing scripts in the file. Are they yours? This, of course, is right up my alley. Be sure to check out www.kolvensystems.com if you haven't yet.

More to come on other projects...E_G

Friday, August 19, 2005

Have you guys checked out Excel User®'s book on dashboards?

I just recently stumbled upon Charley Kyd's definitive book - Dashboard Reporting with Excel - on creating and managing Excel-based management "dashboards," which are streamlined, multi-metric management reports that visually represent key data and trends in one easy-to-grasp document.

In a past life I worked for a company that was very into management dashboards, and this book would have certainly given me an edge. The book is available for less than $30, but if you're serious about starting a management dashboard system or enhancing the one you've got, spring for the whole kit, which is still less than $50.

I say, get a copy, read it.

Out.

Wednesday, August 17, 2005

Another project...separate names from a huge list

Yesterday I received another Excel project: a 19,000+ list of people's names, all in the same field -- i.e. First Middle Last Suffix. The kicker was that not all people had middle initials. Some had middle initials, while others had full middle names. and Some were Jr.'s, II's, etc. Then, of course, there's dealing with "Von Lastnames" and "van Lastnames" and "Del Lastnames" and "De Lastnames" and "De La Lastnames." It's crazy.

My task was to separate them all into First Name, Middle Name/Init., Last Name, Suffix fields.

I did it without using any VBA, though in my spare time I'm going to try to figure out how to use a Bayesian Algorithm and some code to accomplish the same task.

Thanks, Craig!

Tuesday, August 16, 2005

Thought I'd add an image to my profile

I thought I'd add an image to my Blogger profile, so here it is...

Friday, August 12, 2005

Geez..that was fast...my first $50 project

Boy, maybe I should rethink this $50 thing...

Within hours of making this change, I've already received my first request, completed the project, and have sent the code to the requestor.

Jim, I hope it does exactly what you asked for. I pretty sure it does.

What Jim needed was a macro in an Excel file that would check each cell in a selected range, and color-code the text in the cell based upon these criteria:

  • If the cell contained a numeric value that's not a formula, it's red.

  • If the cell contained a numeric value that is a formula, but only because it's preceded with an equal sign, it's green.

  • If the cell contained a numeric value that is a formula with operators other than merely having an equal sign, it's blue.

Using a regular expression, the isNumeric function, some nested If_Then_Else's, and a few other things, I think I nailed it down pretty good.

Thanks for the project, Jim, and for $50.

Made a few changes to the blog yesterday...

and will continue today.

1) I've changed the heading. $50? Why not? Not sure when I'll have time to do the projects (assuming people take me up on it), but it'll be fun.

2) I'm adding in some links to other Excel or VBA blogs that I've found and like. Many of these I've actually gotten ideas and fixes for bugs from.

Later,

E_G

Thursday, August 11, 2005

Fixed an annoying little problem with my email parser

I was having trouble with error handling on my email parsing macro. Whenever the script tried to return a bad URL or one that could not be returned as text, I was using "On Error Goto..." and directing to an error handling branch that ran (marking the URL as "BAD" by coloring it RED and then directed back to the next URL.

This would run just fine once (it could handle one bad URL), but when it encountered another bad URL, it would puke.

I have since changed to using "On Error Resume Next" and following the two lines of code prone to erroring out, I placed this bit of code:


If Err Then
GoTo BAD_URL ' Error Handling Branch
End If



Then, at the end of the error handling branch, just before directing back to the next URL, I cleared the Err object.


Err.Clear



Now it can handle errors an unlimited number of times. I'm so happy.

Later.

Wednesday, August 10, 2005

Here's the Google page parser

Here's v1.0 of the code...


Sub Parse_Google_Search_Results_Pages_For_URLs()

'**********************************************************
'****************** Code by Excel_Geek ********************
'**********************************************************

' This Excel macro calls up search results pages of a Google search
' using a search term specified in a cell named "SEARCH_TERM". Then it uses a
' regular expression to glean from this page all of the webpage addresses (URLs)
' of the search results and places them in a list in the spreadsheet.

' Setting up the regular expression that recognizes search result URLS on Google pages --
' The regular expression also ingores case, which by the way, i convert all to lower in the end.

Set regEx = CreateObject("VBScript.regexp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = """http://([^6""]+)"""

' Now I reformat the search term as typed into the search term as used in Google search query URLs.
' i.e. I replace spaces with pluses and quote marks with %22s.

Range("SEARCH_TERM").Select
Selection.Replace What:="""", Replacement:="%22", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=" ", Replacement:="+", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Set the top limit on the Google page of results to parse.
PAGES = Range("GOOGLE_PAGE").Value

' Need this, apparently, to generate random numbers...
' which we'll need at the bottom of this next FOR_NEXT...

Math.Randomize

' Create a FOR_NEXT loop to get all pages.
For x = 1 To PAGES
' Set the GOOGLE_PAGE value to the page to parse.
Range("GOOGLE_PAGE").Select
ActiveCell.FormulaR1C1 = x

' Selected the SEARCH_URL, as this will need to be passed to the objHTTP.Open command.
Range("SEARCH_URL").Select

' Get the page using the objHTTP object.
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objHTTP.Open "GET", Selection.Value, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")

' Define the pageToParse as teh response text of the "GET" method.
pageToParse = objHTTP.responseText
' Clear the objHTTP object.
Set objHTTP = Nothing
' Set expressionmatch as the strings matched using the regular expression defined above.
Set expressionmatch = regEx.Execute(pageToParse)

For Each expressionmatched In expressionmatch

' Got to get rid of any results on Google's servers.
If InStr(1, expressionmatched, "google", 1) > 0 Then

GoTo MOVE_ON

Else

Range("FIRST_URL_HERE").Select
ActiveCell.FormulaR1C1 = LCase(Left(Mid((expressionmatched), 2, 300), Len(expressionmatched) - 2))
Selection.Offset(1, 0).Select
ActiveWorkbook.Names.Add Name:="FIRST_URL_HERE", RefersToR1C1:=Selection

End If
MOVE_ON:

Next

' Setting up code to pause by a random amount of time between 4 and 15 seconds between pages of results.
Dim newHour, newMinute, newSecond, waitTime
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + Int((15 - 4 + 1) * Math.Rnd + 4)
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Next x

End Sub

Tuesday, August 09, 2005

New stuff coming

I've been working on some code that will help me generate a list of URLS from which to parse email addresses. Using Google as a search engine, i'm able to generate a targeted list of URLs using keywords relevant to my audience.

I'll show you once i've got a decent V1.0 ready...

Monday, August 08, 2005

Tiny little mod to email system

I added a tiny piece of code to the email system so that it checks to see if each email is marked as "BAD" or "REMOVED" before sending it.

More small updates to follow.

Tuesday, July 26, 2005

Emailing system is running great...except

Things are humming along nicely.

I'm able to specify a list of web pages on which there are email addresses for people whom I believe will be interested in hearing about what I'm selling. Then I run a macro that views each page, parses from them the valid email addresses, compares those addresses to those already in my list, and if they are not in the list, adds them to the bottom of the list. Once I've gathered the email addresses, I can programmatically send each one an HTML formatted email, and mark the date and time of each sent email. Finally, when I receive any bad email bounce back messages or "remove me" replied, I can programmatically find the email addresses in the list and mark them as either "bad" or "removed", so that messages are no longer sent to those addresses.

Things I still need to do is make it easier to load a customized HTML email message -- sort of like building a library of messages, which I can programmatically specify. Right now I edit the HTML code each time I want a slightly different email message.

Also, I still haven't heard back from the guy who wrote Outlook Redemption, so I'll have to try to figure out on my own how to specify a particular email account from which to send emails, so that they don't accidentally "spill over" into my Gmail account, even though it's not the default account set in Outlook.

Another thing I'll do is add a macro to automatically mark email addresses with date and time stamps for positive reactions to the message -- i.e. views of certain web pages, requests for more info, etc.

It's getting pretty slick. Stay tuned...

Tuesday, July 19, 2005

Hooray for Outlook Redemption!

A key piece of using VBA to automate the sending of emails using Outlooks is a little .dll called Outlook Redemption


Outlook Redemption works around limitations imposed by the Outlook Security Patch and Service Pack 2 of MS Office 98/2000 and Office 2002 and 2003 (which include Security Patch) plus provides a number of functions to work with properties and functionality not exposed through the Outlook object model. What's this mean? You don't get those little "A program is trying to send an email on your behalf..." messages or the 5 second waiting period.

With Outlook Redemption you can...

  • Make your VB and VBA code run unaffected by the Security Patch.
  • Access properties not exposed by the Outlook Object Model (internet message headers, sender e-mail address and hundreds more properties)
  • Directly access RTF body of any Outlook item
  • Import MSG, EML (RFC822) and TNEF files
  • Export messages to MSG, EML, TXT, HTML, TNEF and vCard formats.
  • Directly access message attachments as strings or as arrays without saving them as files first
  • Display Address Book
  • Force immediate Send/Receive (Tools | Send/Receive in Outlook)
  • Track new e-mail events with (unlike Outlook) new e-mail item passed to your handler.


Redemption supports Outlook 98, 2000, 2002 and 2003 (Outlook 97 is not supported).

I say...check it out if you haven't already.

One thing I'm unsure how to do (either using Outlook Redemption coding or standard VBA for Outlook) is how to specify a particular email account from which to send. Right now I've got two email accounts running through Outlook -- my business email and my personal Gmail POP account. Once in a great while an email I automatically generate and send using my code sends using the Gmail account...

I've emailed Dmitry Streblechenko (Outlook Redemption creator), asking if this can be specified using Outlook Redemption, and I'll post what I find out.

Later.

Monday, July 18, 2005

Doin' some really sweet Excel and Outlook integration

I'm convinced that using VBA, one can automate and integrate Office apps how ever he or she chooses. I've got a really sweet system of collecting and sending emails and automatically marking and deleting bounce backs, "remove me"s, and positive responses using Excel and Outlook. With a bit more coding and tweaking...who knows what is possible.

Wednesday, July 06, 2005

Okay...here's the first version of the code

Should be obvious what it does:


Sub Parse_Emails()
'
' Parse_Emails Macro
' Written by Excel_Geek
'

'I JUST ADDED THIS TO ACT AS A SAFEGUARD AGAINST ACCIDENTALLY CLICKING THE "GET EMAILS" BUTTON

varAnswer = MsgBox("Are you sure you're ready? You've got the URL range and the starting email cell set right?", vbYesNo, "HOLD ON THERE...")
If varAnswer = 7 Then
Exit Sub
End If

'SELECT THE CELL INTO WHICH THE FIRST EMAIL ADDRESS FOUND CAN GO
Range("C2").Select

'DEFINE "cel" AS EACH CELL WITHIN THE RANGE DEFINED BELOW
Dim cel As Range

'SET THE RANGE OF THE URLS FROM WHICH TO PARSE EMAILS
For Each cel In Range("A2:A49").Cells

'SET THE VALUE OF EACH CELL AS THE "WEB_PAGE" FROM WHICH YOU'LL PARSE EMAILS
WEB_PAGE = cel.Value

pageParseRequest = WEB_PAGE
If pageParseRequest <> "" Then

Set regEx = CreateObject("VBScript.regexp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = "([a-z0-9\._-]+@+[a-z0-9\._-]+\.+[a-z]{2,4})"

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objHTTP.Open "GET", pageParseRequest, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")

' If objHTTP.Status <> 200 Then ' Could not retrieve SOAP message
' Response.Write objHTTP.Status
' Set objHTTP = nothing
' Response.End()
' End If


pageToParse = objHTTP.responseText
Set objHTTP = Nothing
Set expressionmatch = regEx.Execute(pageToParse)

'varAnswer = MsgBox(expressionmatch.Count, vbOKOnly, "NOTICE!")

If expressionmatch.Count > 0 Then
For Each expressionmatched In expressionmatch

ActiveCell.FormulaR1C1 = expressionmatched.Value
'varAnswer = MsgBox(expressionmatched.Value, vbOKOnly, "NOTICE!")
ActiveCell.Offset(1, 0).Select

'Response.Write "<B>" & expressionmatched.Value & "</B> was matched at position <B>" & expressionmatched.FirstIndex & "</B><BR>"
'Response.Write Right(expressionmatched.Value, Len(expressionmatched.Value) - 7) & "<br />"

Next

'Else
'Response.Write "<B>" & regEx.Pattern & "</B> was not found in the string: <B>" & StringToSearch & "</B>."


End If

End If

'Response.Write( "<xmp>" & pageToParse & "</xmp>" )

With cel.Interior
.ColorIndex = 43
.Pattern = xlSolid
End With

Next

End Sub



Sorry about the commented out remnants of web programming in there.

Thursday, June 30, 2005

oh....this is going to get good...

Henry's helping me make something very special. I'm not going to let the cat out of teh bad yet, but i'll tell you this: it will work with the email sending macro that i just wrote in Excel.

stay tuned...

Wednesday, June 29, 2005

Okay...here's the code for the email blasting macro

Okay, okay...i was a bit lazy and copped out a bit when i told you to comment if you wanted to see the code for my latest macro, so here it is in all its glory...




Sub Send_Out_Emails()
'
' Send_Out_Emails Macro
' Written by Excel Geek
'

'DEFINE "cel" AS EACH CELL WITHIN THE RANGE DEFINED BELOW

Dim cel As Range
'SET THE RANGE OF EMAILS TO WHICH TO SEND
'YOU MAY WANT TO BE WORKSHEET OR EVEN FILE SPECIFIC IN NAMING THE RANGE,
' DEPENDING UPON HOW YOU USE AND EXECUTE THE MACRO

For Each cel In Range("B2:B201").Cells

'SET THE VALUE OF EACH CELL AS THE "EMAIL_ADDRESS" WHICH YOU'LL SET FOR EACH EMAIL LATER
EMAIL_ADDRESS = cel.Value

'VALIDATE THE EMAIL ADDRESS
'THANK YOU, HENRY (www.azule.info) FOR THIS REGULAR EXPRESSION

Set regEx = CreateObject("VBScript.regexp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = "^[a-z0-9\._-]+@+[a-z0-9\._-]+\.+[a-z]{2,4}$"
If regEx.Test(EMAIL_ADDRESS) = False Then
'IF THE EMAIL IS INVALID (="False") THEN I TYPE "invalid email addresss" IN THE CELL JUST RIGHT
cel.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "invalid email address"
ActiveCell.Offset(0, -1).Select

'OTHERWISE, IT'S A VALID EMAIL AND I SEND AN EMAIL TO THE ADDRESS
Else

'CREATE A MAIL ITEM IN OUTLOOK
Set oolApp = CreateObject("Outlook.Application")
Set Email = oolApp.CreateItem(0)
'CREATE A SAFEMAIL ITEM USING OUTLOOK REDEMPTION DLL
Set msg = CreateObject("Redemption.SafeMailItem")
'LOAD THE SAFEMAIL ITEM AS THE MAIL ITEM
msg.Item = Email

'CREATE THE HTML EMAIL BODY
'ALWAYS HAVE THESE FOUR LINES FIRST FOR AN HTML EMAIL

MailBody = "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD W3 HTML//EN"">"
MailBody = MailBody & "<HTML>" & vbCrLf
MailBody = MailBody & "<HEAD><TITLE></TITLE></HEAD>"
MailBody = MailBody & "<BODY>" & vbCrLf
'NOTE: IN THE HTML BODY YOU MUST DOUBLE QUOTATION MARKS (LIKE THE ONES AROUND HYPERLINKS, ETC)
' AS VBA WILL THINK YOU'VE ENDED THE LINE OTHERWISE, AS EACH LINE BEGINS AND ENDS (IN VBA)
' WITH OPENING AND CLOSING QUOTATION MARKS

MailBody = MailBody & ""
MailBody = MailBody & ""
'CLOSE THE BODY AND HTML TAGS
MailBody = MailBody & "</BODY></HTML>"

'SET THE EMAIL ADDRESS, SUBJECT LINE, AND HTML BODY (AS WRITTEN ABOVE) TO EACH EMAIL TO SEND
With msg
.to = EMAIL_ADDRESS
' NOTE: BE SURE TO SET THE VALUE OF THE SUBJECT LINE PROPERLY!!!!
.Subject = "This is the subject line of the email"
.HTMLBody = MailBody
.Send
End With

'NOW THAT EMAIL IS SENT, I TIME AND DATE STAMP WHEN EACH IS SENT
' IN THE CELL TWO COLUMNS TO THE RIGHT

cel.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=now()"
ActiveCell.Copy
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.Offset(0, -2).Select

End If

'MOVE TO THE NEXT EMAIL ADDRESS IN THE RANGE SPECIFIED
Next


End Sub

Tuesday, June 28, 2005

sorry so long away...

Wow...how long was i asleep?

Anywho...i'm bored with the automatic stock picker excel file...today i finished (it'll never be done, really) an excel file that can blast emails in HTML format via Outlook without sending up those annoying "a program is trying to send email on your behalf..." messages. Check out a little program called Outlook Redemption. It's sweet...like a little wrapper for exteneded MAPI, so you don't have to know all that yourself.

Comment if you'd like to know how i did it.

out.

Wednesday, March 23, 2005

Hey - Tyler's got an online version of his legendary newsletter now!

Check it out...Craig helped him...

http://www.kohtz.org/tyler/

Tooting my own horn (again)

Here's an email from my accountant's office...


-------------------------------------------
From: Jan Westerman [mailto:jan@geacpa.com]
Sent: Tuesday, March 22, 2005 5:29 PM
To: Eric Hunzeker
Subject: RE: (removed for privacy)

Thanks, Eric – Fantastic Spreadsheets!!

-------------------------------------------

I am surely the greatest ever...and what I sent her was so simple relative to what can be done...boggles the mind!

Out! - (btw...still getting the excel-based stock trading system fixed...stay tuned.)

Monday, March 21, 2005

Apologies (to whoever) for lack of posts

I was gone over a long weekend at a friend's wedding in the Smoky Mtns in TN. Beautiful (where the tourism craziness hasn't completely taken over).

Anyway...i'm still getting some of the new wrinkles worked out in my Excel-based stock trading system.

I did fart around with a solver-based NCAA tournament bracket picker, but i've got to add in some heavy randomness, skewed by factors associated with the stats...those upsets are just not predictable, you know...(the bracket i picked based upon my judgement as well as one where i flipped a coin for each matchup are not statistically different from the solver-picked one.) Next year, right?

Tuesday, March 15, 2005

working on repairs to the excel-based trading system

This is taking a bit...trying to optimize the way in which the web queries gather and format the data...stay tuned...

Monday, March 14, 2005

By the way...

I've got some killer ideas for a Excel solver-based, optimized NCAA basketball tournament bracket picker, based upon this years stats, head-to-heads, and each school's historic tourney performances...if you'd like to hear more about it, comment...

later

Oh crap! I found a major problem with my excel-based trading system

Turns out that the web query i was using to get the daily data, aggregate it, and then send it to an Access database for storage and complex querying was not using the same OPEN, HIGH, LOW, CLOSE, VOLUME data that is used by the web service (MSN Money's website) that i was using to track the performance of the stocks i was told to buy.

Here's the long and short of it...the OPEN data point i was getting was a calculated value based upon the CLOSE plus/minus the daily change, which is calculated based upon yesterday's CLOSE NOT today's OPEN. Crappy!

That means that the buy signals i was getting, which i wanted to be stocks that gap-opened yesterday and traded on heavy volume, closing near their highs, was not completely true. I was getting more buy signals than I should have been, which explains some of the crappy performances.

I'm getting this fixed and adding in a down-side, balancing trade query...stay tuned...

Friday, March 11, 2005

Decent day (for what the overall market did)

I bought four stocks today, and one i sold at a small loss, but the others were all mildly up, but not to the point where i would sell them (per my rules).

I haven't gotten my downside play rules fine-tuned enough to get them in action yet, but will try to on Monday.

out

Thursday, March 10, 2005

Got pummeled today on the trader system

Today was not good. Three trades...three losses...I wiped out the prior two days' gains, too.

I'll be modifying the strategy a bit (not the rules, mind you...i've not given them a chance to prove their on or off). I'm going to apply the inverse of the rules I'm using to identify stocks to get a nice daily uptick to identify stocks that should drop, so that i can short sell them, and insulate myself against overly up or down overall days.

We'll see how that goes.

Wednesday, March 09, 2005

Quick Excel VBA solution for a friend today

Today I helped a friend devise a quick and easy method of marking entries in a long excel list with a date stamp in the column directly left of the entry. However, if the entry was an edit of a prior entry, the date stamp that was placed there before had to stay put.

Here's the quick of it...


Private Sub Worksheet_Change(ByVal Target As range)


' This macro runs everytime a change is made to any one or more cells in a worksheet.
' The changed cells are defined at "Target".
' Right now this is not written to handle it if you were to change (delete, for example) multiple cells at once.

'We'll specify that we only care about changes made in column "B" or "2" as it's known in VBA.
If Target.Column = 2 Then

' If the change is made to a cell (or more) in the column specified, then we check to see if
' it had been stamped before. If not, we stamp it, otherwise we don't.
If Target.Offset(0, -1).Value = "" Then
Target.Offset(0, -1).Select
Selection.FormulaR1C1 = "=today()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Offset(1, 1).Select
ActiveSheet.Calculate
End If
Else
End If


End Sub

Another fair day for my Excel-based Technical Stock Trading System

Had two system-dictated buys-then-sells today. One was a loser (NOK) and one was a nice win (SCM). Overall the daily return was over .44%, which again, if replicated over and over, yields one heck of an annual return.

Tuesday, March 08, 2005

Had a good day with the Technical Trading system

Today's picks generated by my Excel-based technical stock trading system were three out of four winners, generating an after commissions (but before taxes) daily gain of nearly .8%. Heck, if I can repeat this day after day...I'll really be onto something.

Now...of course...a technical trading system is only as good as the rules you create to find stocks and when to buy and when to sell, etc. But the system I've developed can be duplicated and new rules implemented.

I might even share some of the rules tips I'm learning as I go.

Monday, March 07, 2005

What is this all about?

I am the Excel_Geek. I’ll use Excel to do just about anything.

Need something done in Excel, but don't have a clue how? I'll do it for $50.

Crazy, eh?

I've done Excel projects worth thousands, so why only $50?

1. The challenge.

2. It builds my library of "already done that" solutions.

3. It's fifty bucks. Got $50? <<click & submit a project>>

Wow! I've got a blog!

I am arguably the biggest Excel geek the world has ever known. With exception of pivot tables (for which I'd say I'm only an expert) I pride myself at being able to use Excel to do just about anything. Just ask me...I'm surely the greatest ever!

Oh, by the way, I've also got many other interests, which will surely take up more of this blog that you'd expect.