Stock chart with additional lines

[pcorrea]

Good morning everyone,

I have an excel chart that has a stock plot (OHLC up-down bars) and then a series of lines for additional data (not stock OHLC, but technical indicators). I can open it in calc and it is fine. If I save it in ods format then all of the series become stock series and it is a mess. I would like to generate additional examples but every time I try I get the multiple stock series issue. I even tried opening as columns with multiple lines and then converting the chart type to stock, but again the additional lines become stock series. There seems to have been a thread in the old oo forum, but when I click on it it does not open. Perhaps it has been archived or the resource is unavailable. Any help with this would be greatly appreciated.

Thanks!

Paul

[Hagar Delest]

Hi,

Calc is much limited regarding Charts compared to MS Excel. can you upload the file or an extract so that we check (see How to attach a document here)?

[pcorrea]

Hi Hagar,

I stripped away as much as I could and got the file down from 7MB to 642KB, still to big to upload. Every time I delete the macros they are still there when I open the file again, perhaps they are attached to the buttons which I cannot figure how to delete. I've never used any of the other services. Can I PM the file? Thanks,

Paul

[jrkrideau]

Probably the best way is to upload to something like like MediaFire or Dropbox and post the link here.

[pcorrea]

Hi,

I'm always a little slow on the uptake for new things. I'm working on a reduced data set version to upload. In the meantime I have tried overlaying a transparent line graph on top of a stock chart and mostly it works well. It just seems that once in a while when I refresh the data the axes of the two charts shift relative to each other slightly. Is there a way to assign the axis positions and lengths directly within the chart? Thanks for looking everyone!

Paul

[pcorrea]

Hi Everyone,

I have attached a very stripped down version of the file. It is just the OHLC stock chart with three added series. These are simple moving averages (which I know can be added as trend lines separately), but these are similar to the other data that I generate in my fortran program and want to plot alongside the OHLC bars. These other data are technical indicators and advanced statistical trends. As you can see in the example, the OHLC comes out fine. The next three series try to start to form another OHLC (if I add another series it will form another OHLC). I really want these to show as lines. Like I said yesterday, I can get 'close' to what I want by overlaying 2 graphs and making one transparent. The only problem is that when I change the data to look at another security the two graphs shift subtly away from each other. So I am either looking for how to do this within a single graph or how to fix two graphs so that their displayed areas (walls in OO I think) never change relative to each other. I generate a maximum and minimum for the graphs so I know how big each should be and have a macro that reassigns these when the data is reloaded so that the scales should be the same.

Thanks in advance for taking a peek!

Paul

[coray80]

This is for stock quotes!?
It sounds like you've put a lot of work into this but I'm now going to tell you 'the right tool for the job'.
http://www.spacejock.com/
The free version of FCharts (FChartsSE).
I used it for some time before I decided I wanted to support the guy who had done all this so I now use Pro but memory sayes that everything you want and more you will find in the free version.

[pcorrea]

Hi Coray80

Thanks for the link. This looks as nice as the tool my broker gives to its big customers! User defined functions have to use their tools and I have developed a mixed genetic algorithm/statistics approach that could not even come close to being duplicated there. I do have problems running wine with some of my other software as well. I was thinking of using gnu plot as an alternative if could not be worked here, but OO has some other nice features as well. Thanks for looking!

Paul

[pcorrea]

Hi everyone,

Still did not find a solution for this in OO. I was able to rebuild and store the .xls spreadsheet (as xls not ods) with oo macros for data refresh. It took about 3-4 seconds to refresh, much better than the 18-20 sec in excel:mac, but still too slow to use. For anyone who is interested, my ultimate solution was to go to gnuplot. I was able to generate stock charts with lines, secondary axes-everything except the simple moving average (gnuplot fits functions but does not do SMA!). I wrote a fortran program to reformat the csv files into gnuplots preferred form and to generate the scripts to cause gnuplot to generate the chart and all the details (even the sma). The slow part is that on my mac mini when I open a postscript file using preview it converts it to a pdf first. Still timing is in the 1-2 second range for a refresh. If I use image Magick to convert the ps to jpg and then open that in preview we are back under a second like I was in excel on XP. gnuplot is a wonderful tool if you want to learn it, it actually took less time to program in fortran than doing the macros in oo, but I am much more proficient at fortran!

Paul