Excel Bubble Chart Code

I am trying to model a pulley system in excel from a code that will calculate belt tension. I have tried to plot the system in matlab but was having trouble scaling the size of the markers to be the correct diameter so I created a matlab code that will generate an excel file and plot the data from the belt tension code as a Bubble Chart. My problem is that I have a specified diameter for each pulley. I can manually set the Bubble size in excel to be the appropriate diameter but given that we are going to be constantly changing these values, having matlab set the bubble size would be ideal. Currently it models each pulley as the same size in their appropriate X and Y coordinates. Any help would be greatly appreciated! PulleyTable is sample data and below is a copy of the code:

PulleyTable = table({'CRK','IDL3','AT1','ALT','AT2','WP','IDL1','AC','IDL2'}', ...
  [0;123.6;185.27;260.42;228.77;138.84;-62.55;-268.3;41],...
  [0;2.2;46.86;58.25;127.35;190.34;182.38;98.3;121],...
  [164.16;62.26;57.26;60.56;57.26;124.36;72.26;116.26;72.26],logical([1 0 0 1 0 1 0 1 0]'),...'VariableNames',{'Name','X','Y','D','In_Out'});
PulleyTable.Properties.VariableUnits({'X','Y','D','In_Out'})={'mm','mm','mm','CCW'};
filename = 'pulleygraph.xlsx';
writetable(PulleyTable,filename,'Sheet',1,'Range','A1');
Excel = actxserver('Excel.Application');
ResultFile = ['...\pulleygraph.xlsx'];
Workbook = invoke(Excel.Workbooks,'Open',ResultFile);
set(Excel,'Visible',1);
Chart = Excel.ActiveSheet.Shapes.AddChart;
Chart.Name = 'NanoPulley';
ExpChart = Excel.ActiveSheet.ChartObjects('NanoPulley');
ExpChart.Activate;
try
  Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
  invoke(Series,'Delete');
  Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
  invoke(Series,'Delete');
  Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
  invoke(Series,'Delete');
  Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
  invoke(Series,'Delete');
  Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
  invoke(Series,'Delete');
  Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
  invoke(Series,'Delete');
  Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
  invoke(Series,'Delete');
  Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
  invoke(Series,'Delete');
catch e
end
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(2)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(2)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(2)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(3)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(3)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(3)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(4)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(4)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(4)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(5)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(5)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(5)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(6)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(6)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(6)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(7)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(7)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(7)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(8)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(8)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(8)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(9)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(9)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(9)];
NewSeries = invoke(Excel.ActiveChart.SeriesCollection,'NewSeries');
NewSeries.XValues = ['=' 'Sheet1' '!B' int2str(10)];
NewSeries.Values = ['=' 'Sheet1' '!C' int2str(10)];
NewSeries.Name = ['=' 'Sheet1' '!A' int2str(10)];
Excel.ActiveChart.ChartType = 'xlBubble';
Axes = invoke(Excel.ActiveChart,'Axes',1);
set(Axes,'HasTitle',1);
set(Axes.AxisTitle,'Caption','X')
Axes = invoke(Excel.ActiveChart,'Axes',2);
set(Axes,'HasTitle',1);
set(Axes.AxisTitle,'Caption','Y')
Excel.ActiveChart.HasTitle = 1;
Excel.ActiveChart.ChartTitle.Characters.Text = 'Nano Pulley System';
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit;
Excel.delete;
clear Excel;

[dpb]

The question asked is Excel, not Matlab; the place for that would in an Excel forum/newsgroup.

..." tried to plot the system in matlab but was having trouble scaling the size of the markers to fit the input data"

OTOH, if you'll show us what you've done and provide some sample data folks can use to replicate the plot, more than likely someone here can solve the issue in Matlab in "much more simpler" fashion.

[Cameron Martineau]

..... I provided my matlab code and sample data in my question

[dpb]

It's trying to manipulate Excel, though...and it won't run here...it took about a half-dozen restarts to get this far and I don't know Excel lingo enough to try to work out the bugs in an older release...

>> cam
Error using Interface.Microsoft_Excel_9.0_Object_Library.Workbooks/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: 'pulleygraph.xls' could not be found. 
Check the spelling of the file name, and verify that the file
location is correct.

...
Error in cam (line 12)
Workbook = invoke(Excel.Workbooks,'Open',ResultFile); 
>>

>>  ResultFile
ResultFile =
pulleygraph.xls
>> dir p*.xls
pulleygraph.xls
>>

Attach the image of the graph you did create and tell us what isn't as you want/what you would expect/want instead.

[dpb]

OK, I figured out the Open; Excel needs fully-qualified name...

    ResultFile = fullfile(cd,'pulleygraph.xls');

did the open Excel, but almost immediately

>> cam
No appropriate method, property, or field AddChart for class Interface.Microsoft_Excel_9.0_Object_Library.Shapes.
Error in cam (line 14)
Chart = Excel.ActiveSheet.Shapes.AddChart; 
>>

I give up ...

[Cameron Martineau]

Try putting in the file path for pulleygraph.xlsx for ResultFile, like mine is ResultFile = ('C:\Users\cmartineau\Documents\MATLAB\pulleygraph.xlsx'); it should work then. Regardless, here is what the excel file looks like after it is generated currently

[dpb]

I don't understand that at all...each is same size, not representative of Diameter column. What's the actual objective here?

A Matlab scatter graph that does represent actual area in correct proportions is in Answer below.

Answer 1

[dpb]

pt=PulleyTable;  % shorten the name...
scatter(pt.X,pt.Y,pt.D.^2/6,'filled')
xlim([-400 400]), ylim([-50 250])
grid on

yields

To make the sizes proportional to the actual diameters, use pi/4*pt.D^.2 and then you can scale that by any proportionality factor you wish to create the pleasing size of the circles desired for the plot.

The color is a detail; add a color triplet vector as desired; didn't worry about it at this point until figure out what it is that actually are trying to accomplish.

[Cameron Martineau]

Yes that's much simpler thank you

[dpb]

No problem! :)

While it may take some digging, just have to read the doc's carefully to follow the meaning for the various inputs. In the description of the area argument, it notes it is the area of the patterns that is proportional to the input, not the radius or other distance measure. Hence, to produce something that reflects that area from a distance, one needs the squared term as the scaling variable.

[dpb]

BTW, you can fix up that plot some that might be useful visual effect...

hold all
hSc(2)=scatter(PT.X,PT.Y,10,'k','filled');

yields

where the center is highlighted with a black dot. Also added 'markeredgecolor','k','linewidth',2 to outline the pulleys.

Salt to suit, of course...