Stock and Bar chart on same chart, is this possible?

33 replies [Last post]
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
Stock and Bar chart on same chart, is this possible?

If so, could someone please help guide me.

ty

JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
I read it was possible to
I read it was possible to make both Charts Transparent so the charts can be overlapped. For some reason this did not work.

Anyone have any solutions.

mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
For the background chart,
For the background chart, keep the background solid.

For the foreground chart you need to make both the chart area and the chart wall area transparent.

LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
Thank you for the suggestion,
Thank you for the suggestion, I tried, for some reason its not working.

could u please show pics of what I need to do.

ty

mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Attached example histogram
Attached example histogram with xy scatter.

Histogram is background, XY is foreground. If you double click on series in either then that particular chart is opened to edit settings.

If you’d like to post sample, I’ll check if my macro can be modified to suit.

AttachmentSize
Test4.ods 21.56 KB
LibreOffice 5.1.3.2, Windows 8.1
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Do you just need to select
Do you just need to select “Stock Chart 3” or “Stock Chart 4” chart styles, or do you need some special format?
LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
Now that’s a beautiful chart.
Now that’s a beautiful chart. I did exactly what you have done but mine will not over lap.

Can I have two different data sets and over lap charts?

Here’s my friends file I’m trying to over lap ,

Could u please over lap graphs and send back, plus could u please show me how to combine data into one graph
with different colors for different sets of data for each sheet.

I’ve attached file.

ty in advance for any help you provide.

AttachmentSize
SPY DATA with formula - from External Link Yahoo Finance.ods 114.62 KB
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Try to create a simple stock
Try to create a simple stock chart first and then add a second data set to the same chart. I don’t think you need to overlap two charts to show two stock charts in the one chart, check if attached is close to what you want.

Charts in your spreadsheet were just simple barcharts, so try the following:

Reference for creating Stock charts at this link

First start a new sheet to put the data in the required column order, this could be a new sheet in your existing document, but I’ve attached example only for the new sheet.

As described in the link, the order of columns is important to create a stock chart, I’ve assumed you want to include volume so my example uses Stock Chart 4, I labeled row 1 for the order of the columns required.

I copied a small part of your data into the area below these labels, with the order changed to match the labels. I’m new to stock charts so please check I got this right. I calculated “Close” from “Last” + “Change”, hopefully this is correct.

Select the range of cells from A4:F11, the first data set.

Insert, Chart, and choose chart type = Stock, in the vertical list of chart types, then select the fourth chart on the right side, this is Stock chart type 4.

In the chart wizard Click next twice, or select step 3, data series.

In the list of data series, select Volume, then click Add.

Select name under data ranges, click box with green up arrow to the right of “range for name”, then select cell for heading of the volume column of the second data set

Select y-values under data ranges, click box with green up arrow to the right of “range for y-values”, then select data cells of the volume column of the second data set.

In the list of data series, select Close, then click Add.

For each of the items listed under data ranges, repeat the above steps to set each to the correct range of cells.

click finish in the chart wizard.

You can double click the chart and select data ranges in the toolbar if you want to modify the data series used in the chart.

AttachmentSize
Test5.ods 22.83 KB
LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
thank you for the help. I
thank you for the help. I think I got it now. (data) but still have trouble Over-lapping charts.

I’m still having trouble creating two different chart types, is this even possible?

Bar chart and a stock chart in the same chart. if its not possible,

could u please show me with the attached file. please overlap charts on sheet named “spy historical prices”

ty u again for all the help you have provided.

AttachmentSize
test 2016-05-18-StockAndOptionQuoteForSPY.ods 66.37 KB
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Looks like you would want the
Looks like you would want the barchart as background, so first change the z-order of the two charts. Rightclick on the bar chart, select Arrange, Send to back. Then you can position the stock chart over the top of the bar chart.

Alternate is to single left click the chart and then use the “Send to Back” icon in the toolbar. (Not the “To Background” Icon.)

Looks like you already set the stock chart transparent, so probably only the z-order that stopped you getting this to work.

For position and size to match there are two stages.

1) The full chart area of both charts should be adjusted to the same position and size.
Right click each chart, select “Position and Size”, And type in the position and size as this makes it easier to set both charts to have the exact same values.

2) The chart wall should also be adjusted to the same position and size within the charts. This can be tricky, but double click a series within the chart to adjust and then single click selecting the top edge of the chart wall seems to work. Then again right click and set position and size, in this case position is relative the chart area.

Then you probably need to format axis labels and legends, decide which ones to keep and where to position etc.

AttachmentSize
test_2016-05-18-StockAndOptionQuoteForSPY[1].ods 73.97 KB
LibreOffice 5.1.3.2, Windows 8.1
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
When you set position of the
When you set position of the chart wall, make sure to leave space from edge of chart area for the axis labels, otherwise Calc will change the position and the two charts will not match position with each other correctly.
LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
Awesome, this is exactly what
Awesome, this is exactly what I want, but is there a way to line up current stock price with Bar chart value.

example: stock price $205

is there a way to have the bar chart match the price of the stock price. Bar chart price levels are extremely wide compared to the stock price chart.

How do I add price intervals between 250 – 200 – 150 levels, how do I get it to show increments of 5 200,205,210….
and then to accurately represent on the bar chart.

please review file for what I’m trying to represent.

what I’m trying to do is match up stock price to bar chart price level.

BTW: is there a way to have this automatically update on its own, so I do not have to keep copy/pasting data each night?

extremely thankful for the help!

AttachmentSize
test 2 2016-05-18-StockAndOptionQuoteForSPY.ods 77.48 KB
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
So if I understand correctly,
So if I understand correctly, you want the category axis of the bar chart to align with the vertical values of the stock chart.

With a bar chart the category axis is determined by the source data labels. In your example data this is not linear, You have some categories on 0.5 spacing but some are on 1.0 spacing. To get the alignment to work you would need to add categories so they are all on regular spacing, with zero values if no data is to be plotted for those values.

Also related to this, your stock chart has values below the lowest value of the bar chart minimum category, so you should include extra categories to cover the range of the stock chart. The same would apply to values above the maximum category.

As you also want to make it easier to update the data, I would suggest creating an intermediate data table sheet for the bar chart. Top row reserved for column labels, so in cell A2 put the minimum value to plot in the stock chart, maybe =Int ( Min ( …. ) ) . In A3, = A2 + 0.5, and copy this down the sheet until you reach the maximum value for your stock chart.

Then use VLOOKUP to select the data, for calls and puts, from your source data sheet using the value in each row of column A as a key into your input data.

Then use this table to create the bar chart.

Overlay the stock chart and the bar chart as described in the earlier post.

As the category labels on the bar chart align with join between the two bar chart series, you want to set the start and end of the stock chart y-axis to line up with the joints between bar chart series. This needs to be done by adjusting the chart wall position and size.

Make sure to keep the primary and secondary y-axis scales of the stock chart identical to each other, and adjust them to match the minimum and maximum category of the bar chart.

Turn off the labels for the primary y-axis of the stock chart, so they don’t get confused with the bar chart labels.

Also use VLOOKUP to select data from input sheet for the stock chart table. Then you should be able to change input data and this will automatically update the charts.

See if you can make the above changes to your workbook, then we can see what else we need to fine tune.

LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
thank you for the info, (I’m
thank you for the info, (I’m a novice when it comes to LibreOffice.)

This will take some time to adjust, or if you could help me out.

i have used =index , =rank , match functions, but not Vlookup (learning a lot from this site and utube video’s)

mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Index and Match seemed to be
Index and Match seemed to be better than Vlookup for this application as Vlookup expects the key to the table in the first column.

Attached uses reference to a sheet that I think you already manually created from raw data. Is this sheet required or was this just an intermediate step to the chart?

You could modify the cells N1 to N4 to change the location of the source data for the calls and puts table.

Adding to history data, you can select the next blank line, but then use insert cells, this will grow the table used by the calls and puts table, but not the source data to the stock chart. Edit the chart source data for that with this version.

Source data for bar chart also needs to be updated in chart source data with this version.

Trying to manually adjust chart alignment is tricky to get exact match. Attached is close but could be improved by adding macro code. Also macro code could update the chart source data.

I adjusted formatting of charts to try and improve appearance.

Give me a sample of raw data, without any manual edits, and if I get time I’ll see if it can be automated.

AttachmentSize
StockAndOptionQuoteForSPY.ods 78.44 KB
LibreOffice 5.1.3.2, Windows 8.1
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Updated version with
Updated version with Macros.

This version has a push button on the chart sheet which refreshes the data in the charts and corrects the alignment of the stock and bar charts.

Does not recreate the charts and is also dependent on data in correct location within the sheets of the spreadsheet document.

It’s a bit tricky to align the two different chart types as the Chart Diagram size also includes dimension of labels and tick marks, but using the Axis size and positions seems to be working quite well.

AttachmentSize
StockAndOptionQuoteForSPY.ods 82.59 KB
LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
WOW, u r truly a master of
WOW, u r truly a master of LibreOffice.

This is exactly what I’m looking for. How did u create this master piece,

it said Macros was disabled due to possible virus? How do ppl put viruses in macros

Can I create multiple charts this way?

still learning

mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Macros can be dangerous as
Macros can be dangerous as they are programs and can easily open malicious websites or alter registry values. If I download from an unknown source I usually open first with macros disabled and take a quick look through the code for anything that seems dangerous.

I’m recent to LibreOffice, but long time user of Excel VBA for a few large projects that should probably be in C++. Evaluating LibreOffice as a possible interface to data and charts for one of those projects to break dependence on Microsoft Office.

I try to help on this forum as a way to explore the capabilities of LibreOffice, that I might otherwise not think to test. So far I don’t find any major problems, although it can be a bit more difficult to find information and the BASIC IDE is not so friendly. Seems to run a bit slower than VBA but then even VBA is not intended for heavy processing, both are very convenient for mixing automated and manual manipulation of data.

The macros I included in the workbook were based on those I used for the histogram combined with XY scatter, but showed a few more differences that I had to work around with chart sizing, so made it an interesting exercise for me. There might be some better methods that I didn’t find yet.

I’m not sure how useful they will be to you, if you already have some programming experience you should be able to pick up LibreOffice Basic and use these as a starting point.

You should probably expect the forums to be good for specific questions and problems, but consider this particular exercise as a special case because it caught my interest.

You can create charts from Macros, or modify existing charts as in this case. Check the other LibreOffice resources online for examples. I may post my histogram macro as example at some point, but not until I tidy it up a bit.

LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
thank you for your knowledge
thank you for your knowledge and expertise.

Is there a way you could create a file for me where I can upload or automatically upload from yahoo finance Historical prices and Option chain expiration, to create a chart as you did and have it automatically upload with a control button.

I don’t need current intraday data, just end of day data. speed of VBA dont think it would be important?

If I have this in place it will be easier for me to see and learn from.

I’m a novice at programming, but willing to learn. just need a starting point.

I see how you did SPY TABLE, nicely done.

I uploaded a file SPY historical data, June 17 expiration Option chain List and Straddle settings

everything looks ok, but when I import Straddle layout, it does not layout correctly.

would u be able to convert this file as you did on previous file.

thank you again for helping.

AttachmentSize
SPY Historical price data Option June 17 expiration List - Straddle sheets.ods 58.48 KB
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Does the Straddle format
Does the Straddle format sheet have any additional value compared to List format?

It looks very easy to convert from List format to the SPY Table sheet of my previous example, it wouldn’t take much to create a macro for that.

From Straddle format might also be done, but certainly not easy, due to mixed text and values in some cells. It might be easier to take the List format and then create something like the straddle format if it’s needed.

LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
Straddle view does not have
Straddle view does not have any additional value compared to the list. The reason I was copy / pasting using Straddle view is the strike prices would match up. List layout strike prices would not line up when I was making my charts without Macros. (since I do not know how to create a Macro)

If this can be done from the List layout would be great and all the strikes are able to match their volume and Open Interest values, then there would be no need for the straddle view.

Please can you help teach me how to write Macros, create the beautiful chart as you did. I’m a quick learner.

What I would like is to have the chart update with a touch of a button. Historical price chart a long with the Option data and Price levels match up, as you have shown on previous chart.
But of course as options expire I would have to be able to create a new chart each time there’s a new set of expiration dates.

As the chart updates will the value lines always match-up?

do u mind if we PM

mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
I made some further changes
I made some further changes to try and create the charts from your last example source data. Attached code should be loaded into a module under “My Macros & Dialogs”, under “Standard”. Then you can assign the sub CreateStockBarChart to a hot key, I’m using Ctrl+s for testing. This method does not need the macros in the data file, so you can open any workbook with the required data, press Ctlr+s and it creates a new sheet and adds the chart.

Searches for sheet name ending “ List format” to find the strike data. Sheet named “SPY Historical price data” for historical data.

Still some work to do on chart formatting, as it creates new charts each time it doesn’t match the format of the original charts yet.

Your sample data shows a problem in the bar chart, there are too many categories, range from 50.00 to 315.00 in steps of 0.5, with this many categories the bars are very thin, and doesn’t look good. Also the history data is a much smaller range.

I think it might be better if we create a frequency histogram for the bar chart, example collect the strikes 50.00, 50.50 … 54.50, to a single category plot at 52.50 and plot the sum of calls and puts over this range.

I don’t really understand the source data but guess the data is already being collected into bin ranges to discrete values in 0.5 steps. Example does 50.00 strike include data from 50.00 to 50.49, or is it 49.75 to 50.25? Knowing this would help collect the larger bin sizes correctly.

We could try and select the histogram bin size to suit the source data range to look ok in the chart but still convey the correlation between strikes and stock chart.

Using .odt for attached code as unable to attach a .bas file and too much code to include in a code block as it would need to be edited to avoid emoji conversion. I’m changing the style of my code format to try and avoid this but not completed in this module yet.

AttachmentSize
StockBarBas.odt 11.8 KB
LibreOffice 5.1.3.2, Windows 8.1
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Note that I also had some
Note that I also had some issues with the List format source data, as the strikes are hyperlinks and not numbers I found that I need to read them as text strings. This is why the categories are now displayed to two decimal places.

I wasn’t sure if you need to keep the hyperlinks, if not then it might be better to modify the strike values from hyperlink to formatted numbers. I could include this in the macros.

LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 26 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
thank you….. you must be a
thank you….. you must be a master coder (that’s great, wish i knew half of what you know) I’m willing to learn.

if I understand correctly from what i have noticed over the years watching Option strike prices, when the equity stock price gets in a certain range of strike prices CBOE opens up increments of 0.5.

We do not need to have the entire list of strikes, just a range of strike prices, as long that range can be adjusted when the market has extreme moves, but in most cases if we have a range of 200 strikes ( 100 above / below current price with increment 0.5) i believe this will work.

Is there a way to have the chart self – adjust to always display 100 strikes above / below current price?

mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Its only software right It
Its only software right Smile

It can be fun to wind up professional developers with that short question. I’m not professional coder, but still been coding for a long time with lots of bad habits. The real challenge is make it understandable and simple enough to avoid unexpected and unwanted behavior.

Should be easy to limit range to +/- 100 strikes, perhaps with block arrow added to the chart if data is outside the range, similar to my histogram chart.

LibreOffice 5.1.3.2, Windows 8.1
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Version 2 attached. Reduces
Version 2 attached.

Reduces range of bar chart data and chart formatting improved.

AttachmentSize
StockBarV2Bas.odt 13.07 KB
LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 27 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
please review file
please review file
AttachmentSize
Macro file SPY Option List - Historical Prices.ods 42.32 KB
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Macro expects the sheet name
Macro expects the sheet name to end with “ List format”. I expected the date to change so it doesn’t look for a specific sheet name.
LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 27 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
see screen shot of Macro
see screen shot of Macro error, I also updated sheet to “List format” both sheets
AttachmentSize
Macro error.png 169.71 KB
Macro file SPY Option List - Historical Prices.ods 42.15 KB
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
“ List format” should only be
“ List format” should only be added to the List format strike data sheet, don’t add it to the historical data sheet, that sheet needs to be called “SPY Historical price data”.
LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 27 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
here’s the file, i changed
here’s the file, i changed the name to “SPY Historical price data” and made sure “List format” was added to the end of the sheet name.

when i do Ctrl-S i get the error

“A Scripting Framework error occurred while running the Basic script Standard.Module1.CreateBarChart.

Message: wrong number of parameters!”

attached file I’m using.

Spy Historical data imported from Insert – Link to external data
Link i’m using: https://finance.yahoo.com/q/hp?s=SPY+Historical+Prices ( i deleted row that shows dividend )

Option sheet is the same. Link: https://finance.yahoo.com/q/op?s=SPY&date=1466121600

AttachmentSize
Macro file SPY Option List - Historical Prices.ods 39.28 KB
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Should be CreateStockBarChart
I think you might have set ctrl+s to run CreateBarChart instead of CreateStockBarChart.

Could you check and make sure, follow the same instructions I sent by PM.

LibreOffice 5.1.3.2, Windows 8.1
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Following from our PM,
Following from our PM, attached updated version, this time the macros are embedded in the speadsheet.

Config sheet has a list for stocks and data links, you can add to the list, but it tends to crash LibreOffice after about 25 runs when I repeat the links you gave me as an example. You could try with additional samples, it should easily manage 10-12. It needs a unique stock name for each row of data.

Click the button and it will run the list of reports and create the chart for each set of data.

Output is to a new spreadsheet document, which would not contain macros, you can save them if you like, or just run again from the config sheet when needed.

Edit: updated attached to include missing function.

Edit: updated to version 4

AttachmentSize
StockDataCollector_V4.ods 20.53 KB
LibreOffice 5.1.3.2, Windows 8.1
JG101
Offline
Last seen: 49 min 27 sec ago
Title: ★★
Joined: 4 Apr 2016
Posts: 16
test

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.