how to add position lines to an excel stock chart?

"lines where i held a position" What do you mean by this? Do you mean the number of shares held on each date?

Please attach your file so we can see what data you have, see yellow banner at the top of the page.

Here is an example of a stock chart with a line chart that gives holdings by day. Technically you cannot make a combo chart with a stock chart, but there is a workaround of creating the additional series then formatting it as a line on a secondary y axis. Then you have to tweak the min/max of each set axis to prevent overlapping so it is all readable.

almost what i wanted. but i'd like the position line to be with the stock bars, showing entry and exit points.

attached now a spreadsheet. excel fails to show the closing prices, but for the purposes of this, maybe it does not matter...

"i'd like the position line to be with the stock bars, showing entry and exit points."

I don't know what you mean by that. Do you have an example, even if it has nothing to do with your data and is from somewhere else? But the chart I gave you is the only way to show another series with a stock charts. It sounds like you may need a combo chart, which cannot be done with a stock chart.

That might be possible. No idea what time zone you are in (one reason that we ask for location in your profile) but I'll have a second look on Monday.

As I explained earlier you cannot use hi/lo/close charts as a combo chart. However, you can add a series and format the series as a line. To do what you are asking for I had to create another set of data to generate the data for the line. See attached.

You will probably want to change the formatting to your liking.

This chart will not show if you divest your position one day then reacquire the next day. It will be one continuous line.

The position lines follow the daily close price. Not sure if that is what you wanted. You also said "straight lines instead of the curved ones," not sure what you have in mind there. Straight line from the acquisition price to the disposition price? That's a bit harder.

ok - played around with it a bit, but stopped (for now) when i wasn't able to make excel count the duration of holding each pos. i shortened the overall time frame & copy pasted the values to how the lines are generated. weekends screw up the lines not to be straight, and day highs go out of whack due to what i did. work-in-progress...

now i played around with it more. reversed the time span, etc. but any time i try to replace the lines source data, the lines disappear - such as going J to K even though K has the data of L.

do you know why, @69StringJazzer? am i running to excel limitation?

any time i try to replace the lines source data, the lines disappear

If my example shows the type of chart that you need, but you are changing data so it doesn't work, you need to attach the actual data that is giving you trouble. All you attached is my file with a couple of more columns of data.

I don't understand what you mean by "the lines disappear". I see the lines are still there.

https://peltiertech.com/excel-stock-charts/

that graph took me an hour to make, following peltrier's instructions.
Now it's 2 lines and 1 scatter.
The open and close aren't included yet, but that was just a matter of time.
I looked at the graph in #7, not the one in #15.

Now there is a 2nd serie added, to make a difference between gain (red) and loss (blue).
Your graph and my graph are now on the same page.
In "pos" columns E:F, you find the close-prices on those specific dates.
In "QrStckYhCSV"
- I : match the date with those in "POS"
- J : gain (+) or loss (-)
- K : if the date is between an entry and an exitdate in "POS"
- L:M : regression of the close-price in case of "+" or "-"

the chart is built from scratch following peltrier.
I added a 5th and a 6th serie "close" and "open" without a line, only a "-" as marker (black and green). (just select the column and copy with CTRL+C, go to the chart paste special etc and adjust the charateristics of the serie. I'm not so familiar with stockcharts, but is that oké like that ?

I didn' understand "lines that show linear gain/loss of the pos over time". You can create a new column with the desired values and add that one also to the chart.

see formula in column L and read the help for the "forecast"-function, to calculate a straight line for each day in a period.
In attachment a simple example of the forecast-function, so that you can better understand the complex formula of column L

Great job.
How long took it you to achieve this result ?

Please Login or Register  to view this content.

You need 2 dates for the known X and 2 prices for the known Y.
In the offset-function, you give an absolute address, here $c$3 en $E$3 in sheet "pos" and you go as many rows down as needed for the right date. That number is given in column I of "QrStock..." and you want that cell and the cell at the right, so 2 cells, that's the last argument of that function.

Now you want the entry and exit price of tab "pos", so the columns H:I instead of columns E:F, modify the red pos!$E$3 into pos!$H$3 and the formula 'll take those 2 prices.

If you don't use n/a, the serie goes to 0 instead of creating a gap, those vert. lines you mentioned.
You can change the behaviour of excel concerning n/a and gaps, see https://techcommunity.microsoft.com/...ls/ba-p/108453

Do you understand the functions "forecast" and "offset" when you read the help ?


EDIT : as result of my trial, when the result of a formula is "", then the cell isn't blank and the curve goes to zero (your vertical lines), so in the formula you need to replace all the ""-results with a "n/a"-result.
In some of the cells, i deleted the formula which resulted in a "" and the curve gives a gap.

i do understand forecast (with your excellent help, and tutorial @ https://exceloffthegrid.com/interpol...cast-function/) and offset in their simple form. had to create an x y table from the dates in pos to better figure it out.

now i got the desired end result (which was already on your last file), but i still don't fully understand how. if i copy/paste the offset formulas on their own, i get #VALUE. why?

=IF([@[+/-]]="+",FORECAST(K55,OFFSET(pos!$H$3,QrStckYhCSV!$I55,,,2),OFFSET(pos!$C$3,QrStckYhCSV!$I55,,,2)),NA())

if your formula

Please Login or Register  to view this content.

is the one in cell L55, then it's ok, if not then that's why.

it is indeed from L55. see the file on this msg. that's one where i copy/pasted from L85.

so? Now it's working, or does it still give a problem ?

it's working! i was just wondering about copy/pasting the offset -formulas on their own.

and i tried the chart's OHLC-series on another data set, but the formats don't seem to stick.

can you give me an example ?

deleted duplicate post caused by the "tokens" that this site uses.

when you select the x-axis and right-click to modify the settings

https://www.excelforum.com/excel-for...ml#post5596839 #15, the small imperfections you see in the red line are the weekends/holidays.
When the real dates are used in the forecast-function, then the red lines would be 100% straight.