Monday, July 20, 2009

Making Your Own Stock Charts vs. Getting Permission from Big Corporations to Use Theirs


This post is a follow-up to my last article.

I had originally written that article the Friday evening of the last day of its embedded chart (July 10), just over a week ago. It was all done and ready to post in a timely fashion until I hit a snag. I realized I needed permission from ETrade to post the screenshot I had taken of their stock chart (and had Photoshop'd green arrows over). So I emailed ETrade internal support and asked if I could post it as part of an article I was writing in my blog. I told them that the chart already contained the ETrade logo, I was going to put a link back to ETrade, and the article was also favorable to ETrade as well. Was there anything else I needed to do or was this OK. Next day, they referred me over to their Media Relations department. I emailed them with the same questions, but they never replied! A whole week had gone by. I also tried them by phone, but all I got was an intimidating voicemail greeting in return. I figured there was little chance at this point, so why bother then? All they've done so far is waste my time.

I also looked into Yahoo. Sure I'd have to make a new chart, but getting permission might be easier over there? So I browsed their site and actually found some useful information about their process, whereas ETrade had literally nothing. But then Yahoo states it can take up to 10 business days to get the "OK" for a screen snapshot! Who feels like waiting around for that? And besides, it looks like a big, complicated process anyway.

So I decided to make my own graph with Microsoft Excel. Enough is enough. I found it was easy to make a simple chart, but much harder to get something more exactly aligned with what I was looking for. I wanted to show Volume data (Columns) underneath the Stock Price data (Line) to keep them correlated in one nice view, with a common x-axis. I was eventually able to do it, although it took quite a while to figure out how.

Now I'm going to delineate how I made this graph, as it was quite a frustrating experience, and I don't want to ever have to go through it again. It could also help a reader who wants to do something similar as well.

First up, Excel (I'm using version 2003) doesn’t really have a way to create vertical panel charts. Its combination chart type of "Line - Column on 2 Axes" is close, but still doesn't really provide enough flexibility, i.e. the Column data always has to be on the Primary axis. But I wanted "Stock Price" to be on the left side, but found no way to swap the y-axes to do it. There didn't seem to be any other available charting types I could use, either, so I ended up making a custom combination chart.

I first created 3 columns of raw data (Date, Closing Stock Price, and Volume) in a normal sheet. I then created a "Stacked Column" chart from the data, added a Secondary axis, then changed the individual "Stock Price" data series to be a "Line" chart instead of "Column". Then i adjusted the Scale of the right y-axis to squish the "Volume" Columns down. I also adjusted some other cosmetics to make the chart look nicer in general. Finally, I took a snapshot of the chart into Adobe Photoshop where I erased the labels of the lower portion of the left (primary) y-axis and of the upper portion of the right (secondary) axis, in order to make the graph less confusing and easier to read. This feat may be possible in Excel, I don't know, perhaps with custom labels?, but even if so, I wasn't about to spend any more hours trying to figure it out. Finally, I re-drew back in my green arrows, the ones I had first made on my ETrade chart over a week ago. VoilĂ !

So I guess ETrade lost some free advertising here, a link back to their site, and potentially new customers (as well as current customers) to make lots of trades per month with them, i.e. if they were to follow my strategies in this article. Oh well, too bad for them. That's how incompetent they are. They should follow up their leads, or at least answer their customers' questions. So I guess Microsoft Excel will then get all the credit here?, or well at least some of it anyway. Still, their graphing features could be expanded to be more flexible and diverse. If my graph was easier to make, then I would've put an actual link to Excel here.

Actually my graph can even be consider better than ETrade's. Their graph has a plot column for July 3rd where the market was closed (as July 4th fell on a Saturday this year). In my chart, I left it out.

Also another interesting thing to note is that I couldn't find a way to get the granularity of intraday prices in ETrade chart tools nor Yahoo chart tools for that matter, either. So the shortcoming I mentioned in my last article (where we couldn't see more exactly on the graph where I made my trades) would still be there, even with an ETrade or Yahoo chart present.



Copyright © Matt Soscia, MS9, and msnine.blogspot.com, 2009. All Rights Reserved. Unauthorized use and/or duplication of this material, both text and images, in any form or medium, without express and written permission from this blog’s author and owner is strictly prohibited. No commercial or reproduction rights are granted. Excerpts and links may be used, provided that full and clear credit is given to Matt Soscia, MS9, and msnine.blogspot.com with appropriate and specific direction to the original content. This article is available for publishing in your newspaper, magazine, or other periodical, either online or in print. Please contact me for licensing arrangements. Thanks.

1 comment:

  1. I read your post . it was amazing.Your thought process is wonderful.The way you tell about things is awesome. They are inspiring and helpful.Thanks for sharing your information and views.

    share tips

    ReplyDelete