Line chart with markers for events

**vizzkid** · 10-09-2017, 09:38 AM

I'm trying to produce a chart whichs shows
STAGE 1
-a line chart of a financial instrument's daily closing price
-a marker in the line chart (on the line for any given day) if I've bought/sold or received a dividend for the concerned instrument

STAGE 2
-a line chart of a financial instrument's daily closing price
-a marker in the line chart (on the line for any given day) if I've bought/sold or received a dividend for the concerned instrument with a data label showing number of instruments bought/sold and at what price or if dividend then amount for how many number of instruments

I'm not sure if either STAGE 1 or 2 is achievable (if stage 2 works i only need a solution for that), but I've attached a sheet with example input data for both Stage 1 and 2. Hopefully the sheet also helps explain what i'm going for.
I've been able to semi-acheive Stage 1 but it gives a lot of 0's if I there hasn't been a buy/sell/dividend which isn't what i'm going for - i want the axis min/max to be reasonable given the spread of daily closing prices (i.e. not showing 0 as a start point, and the "input instrument" for the chart can vary, so I can't just hardcode the axis min/max)

Any ideas?

**MrShorty** · 10-09-2017, 10:32 AM

The "0"s in your stage 1 attempt are due to the empty strings "" you are returning for blank entries in your data table. Line charts treat any text entry in the data series as a 0 value. If you replace those empty strings with the NA() function, then the line chart will ignore those cells (more detailed discussion here: http://peltiertech.com/mind-the-gap-...g-empty-cells/ )

The only change I see for stage 2 is to add data labels to the chart. Since you have 2013 (assuming your profile is updated correctly), then it should be as easy as adding data labels to each series and "pointing" Excel to the range containing the desired data label text. https://support.office.com/en-us/art...2-f467c9f4eb2d

Does that help?