By amirn on

Hi,

I like to add a chart to my site that shows historical stock prices.
I have an external DB table that holds historical stocks prices (about 10 years worth - few millions of records in all)
I have a CCK stock type that holds basic info about the stocks, but not stocks prices.
I assume having all those historical prices as nodes would have a perfromance penelty on Drupal, so I save them externally.
another reason for saving them externally is that I have a separate process that runs daily and updates new stocks' prices and runs some calculations. I assume doing that on Drupal's own tables may cause integrity problems.

* How can I add a chart of historical prices to a stock's node page?

Thanks.

Comments

keva’s picture

keva commented

can you output the external data as a feed (or CSV, etc) and use the Feeds module to pull them into Drupal?

amirn’s picture

amirn commented

I'll have to do it before-hand after the process runs at time - just have a file per stock or a file for all stocks prepared daily.
(if I'll do that, I'll also have to filter the data in Drupal/Views for the stock and time period requested)

Edit:
sorry, didn't understand you at first, you suggest to import ALL the nodes into drupal everyday? Is having millions of [un-needed(?)] nodes in Drupal DB not going to slow it down considerably?

keva’s picture

keva commented

Okay, I confess I haven't used Feeds (but am interested in it for a couple of use-cases). My impression was that a feed is an XML file that sits on a server, and your Drupal pages would just display the data from that feed, changing the display as the XML file changes.

However, it does look like you'd actually need to import the data into Drupal, then reconfigure it. That would not be good.

modules like Services and Deploy all send Drupal site data out. But I don't see a way to pull data into your Drupal site without importing it. Sorry, this is really out of my realm, but an interesting situation.

(You could do this by adding a Flex - or other - widget to your Drupal pages that read the above-mentioned feed, but that's not exactly a Drupal-based solution)

amirn’s picture

amirn commented

... any ideas?

or maybe Drupal shouldn't be used in my case?

bhosmer’s picture

bhosmer commented

This may be a partial solution, but I will admit I only slightly know what I am doing.

There is a module that imports tables. Table Wizzard. I have seen it used to import a .csv file, and then using mapstraction plot that data on a map.

It seems also to me that since views is basically a sql display based on criteria, you should be able to build a view that displays this. Although, I don't know how you would use views to access an external database. (ie. one external to your drupal database.

http://www.radarearth.com

amirn’s picture

amirn commented

as TW seems to allow using Views on external tables (without the need to migrate or copy them first)!

now I have to see how to link my CCK stock node to a primary key in my external table and I'm on my way.. (well, still way to go finding a charting module to work with that view)

Thanks!

CJBrew’s picture

CJBrew commented

I have played with Table Wizard but I found it's got as many problems as benefits. For one, it's probably not going to get updated for D7. The "Data" module has potential but has zero documentation and is very complicated, and nonintuitive.

So, once I got my head down and wrote some handlers using the Views API I found it was far easier.

http://treehouseagency.com/blog/neil-hastings/2010/01/19/views-handler-e...
http://www.trellon.com/content/blog/view-views-api

You create a custom module, only needs 3 files (see the documentation and examples). In the hook_views_data function you define the table and fields.

Once you've got this up and running (I suggest you try first using a SIMPLE example table) you can access the data directly using Views. Having written the source code there means you've got better direct access to be able to change things when you want to add relationships, joins etc....

It's worth trying, even if you've already got somewhere using TableWizard -- Views API will exist in D7 but TW probably won't.

Views 3 is supposed to be even better at handling external data sources but I have never tried.

dman’s picture

dman commented

We got this happening on this financial site recently.
All the chart work is being done by the Flash App, so the only thing Drupal had to do was maintain links to the appropriate data files.
Have you already looked at the Drupal solutions for chart plugins? there's a handful of quite different ones already built.

.dan. is the New Zealand Drupal Developer working on Government Web Standards

amirn’s picture

amirn commented

How did you get Drupal to link to the appropriate data? not sure that I understood your solution :/

And no, still no charting solution yet. I'm about to look at open-flash-charts and google-charts, but paid-for charting is possible too.
my charting requirements are: line chart to show stock over time with the option to show extra symbols on the graph representing events. also allow the user to select the time period of the graph (last week/month/year or date range)

Thanks.

dman’s picture

dman commented

Well, it's not like there is a drop-in solution that will do exactly that for you without a little work. It depends on the chart solution you go with. This one was provided from I-don't-know-where but it was very configurable and (I think) entirely reasonably priced to license.

But from the Drupal side, we had to
- tell the flash what its configs were
- tell the flash where to get the csv
... so that was just two content fields added to the content type. easy.
then
- render the flash embed with the appropriate arguments
... which I did in the theme when rendering a 'chart' . That was the only bit that required skill and deciphering how to construct the swfobject script using $node->fields

Kudos to all the rest of the magic that happens in Flash.

.dan. is the New Zealand Drupal Developer working on Government Web Standards

amirn’s picture

amirn commented

I was playing with charts and open-flash-chart modules and was able to create a simple chart directly from my external DB (using table migrate module). However, the chart lacks customizing and features I need (or I don't know where to look...), mainly - adding another layer of symbols to indicate events on top of the line-chart.

I saw some commercial flash chart packages that, while not cheap, can do what I need.
my questions are:
- Can anyone recommend a flash charting pacakge?
- Is it possible to add to a drupal node some sort of link to a flash object (I assume I can pass arguments to the flash object such as stock id and date range - so it can display my chart) --- so basically, I'm asking if it's possible to use ANY flash charting package not related to Drupal in any way?

Thanks for the help.

bhosmer’s picture

bhosmer commented

I can't recommend a flash module, but have you looked through some of the statistics modules? I saw a few with charting. It seems like you could use TW and one of the stat modules to accomplish this. I just searched in the modules for "statistics". Some are site statistics, but others are external.

http://www.radarearth.com

amirn’s picture

amirn commented

went over the stats modules and couldn't find anything that is better than the charts modules I'm playing with (charts and views_charts). there are some that support real-time data, but I don't need that.

The views charts module (http://drupal.org/project/views_charts) is nice, but it seems a bit abandoned. It does lack some features found on the charting package it uses (amchart).

The complete Amcharts (http://www.amcharts.com) package looks to have all the features I need at a reasonable price, but I don't know how to integrate it to Drupal :/

Thanks!

bhosmer’s picture

bhosmer commented

It looks like just flash. Add it to a block or page as html I would think.

http://www.radarearth.com

amirn’s picture

amirn commented

but how can I connect that flash page/block to the node of the relevant stock CCK node?
I need a page that contains both stock details from the node and the relevant chart.

dman’s picture

dman commented

I told you above. You provide the extra arguments to the flash however you would normally provide the extra arguments to the flash and that will be different for each library, but you find out the rules, then follow them.

that will be something like settings <param name='datasource' value='my.csv' /> in the flash embed code.
See the docs for your chosen library

.dan. is the New Zealand Drupal Developer working on Government Web Standards

amirn’s picture

amirn commented

Thanks dman.
So, I'll have to prepare a CSV file on-the-fly per each user request (that is, the user selects the stock and the time range, and I'll have to query the DB and prepare a .csv file for the flash lib to work with)

zeezhao’s picture

zeezhao commented

You integrate it via views in drupal. When you create a view, you choose the "Style" as "charts". You then have the ability to pick any of the engines (amCharts, Charts, etc), and Type of Chart etc

See these links for some info:
http://www.lullabot.com/articles/drupal-charting