Excel VBA Pivot Table and Graph

theworm1810

New Member
Joined
Mar 23, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. MacOS
Hello, I am really struggling right now. I have to create a pivot chart with "Industry" as a row field and "Count of Industry", and "Sum of Current Equity" as values. I then have to make a pie graph that summarizes the pivot data. I have no idea how to via code this. I've tried many YouTube videos but I always get errors. I don't know how to use an active code to do this. Any help would be so so appreciated. Thank you kindly.








MACRO.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1idTickerIndustryExchange PriceChange %Marekt Cap52 High52 LowP/EBetaPrevious CloseOpenInstrument TypeSharesPurchase DateAverage Purchase PriceTarget InvestmentCurrent EquityReturn% Change in InvestmentRecommendation
2AB23123AMAZON.COM, INC. (XNAS:AMZN)Diversified RetailXNAS$ 3,135.730.01$ 1,556,952,000,000.00$ 3,552.25$ 1,689.2474.051.13$ 3,091.86$ 3,073.22Stock2.008/22/19$ 2,502.73$ 3,000.00$ 5,005.46$ 6,271.46$ 1,266.000.25SELL
3AC223WALMART INC. (XNYS:WMT)Food & Drug RetailingXNYS$ 132.37-0.01$ 377,398,400,000.00$ 153.66$ 106.8528.160.47$ 133.39$ 132.63Stock10.0011/13/20$ 152.36$ 160.00$ 1,523.60$ 1,323.70$ (199.90)-0.13HOLD
4ZD21870AMERICAN EXPRESS COMPANY (XNYS:AXP)Banking ServicesXNYS$ 144.660.01$ 115,440,900,000.00$ 151.46$ 67.0038.071.29$ 143.30$ 144.76Stock2.007/11/18$ 130.54$ 140.00$ 261.08$ 289.32$ 28.240.11SELL
5HJ874VISA INC. (XNYS:V)Software & IT ServicesXNYS$ 223.12-0.01$ 480,245,800,000.00$ 228.23$ 133.9349.881.00$ 224.70$ 225.33Stock3.0011/8/19$ 253.54$ 300.00$ 760.62$ 669.36$ (91.26)-0.12HOLD
6GD889AMERICAN ASSETS TRUST, INC. (XNYS:AAT)Residential & Commercial REITTSX$ 34.980.02$ 2,073,031,000.00$ 36.60$ 20.1574.121.27$ 34.28$ 34.21Stock100.008/7/20$ 50.43$ 150.00$ 5,043.00$ 3,498.00$ (1,545.00)-0.31HOLD
7DS8098AIRBNB, INC. (XNAS:ABNB)Software & IT ServicesXNAS$ 201.360.01$ 119,852,900,000.00$ 219.94$ 121.5045.541.25$ 200.01$ 195.63Stock32.0012/21/17$ 123.32$ 150.00$ 3,946.24$ 6,443.52$ 2,497.280.63SELL
8JH2398APPLE INC. (XNAS:AAPL)Computers, Phones & Household ElectronicsXNAS$ 124.76-0.01$ 2,108,081,000,000.00$ 145.09$ 53.1533.951.23$ 125.57$ 124.10Stock3.004/16/19$ 120.34$ 150.00$ 361.02$ 374.28$ 13.260.04HOLD
9GT12801NIKE, INC. (XNYS:NKE)Textiles & ApparelNYSE$ 144.990.00$ 228,039,300,000.00$ 147.95$ 60.0082.580.90$ 144.65$ 143.20Stock2.007/1/21$ 154.23$ 165.00$ 308.46$ 289.98$ (18.48)-0.06HOLD
10AW298347THE TJX COMPANIES, INC. (XNYS:TJX)Diversified RetailXNYS$ 67.260.01$ 80,106,110,000.00$ 71.06$ 32.721,092.700.98$ 66.72$ 66.89Stock10.002/2/19$ 66.23$ 100.00$ 662.30$ 672.60$ 10.300.02HOLD
11OH3200Accolade Inc (XNAS:ACCD)Healthcare Providers & ServicesXNAS$ 47.240.02$ 2,550,577,000.00$ 65.25$ 28.6881.300.98$ 46.23$ 45.00Stock10.006/16/19$ 47.24$ 65.00$ 472.40$ 472.40$ -0.00HOLD
12HY32082Biohaven Pharmaceutical Holding Company Limited (XNYS:BHVN)Biotechnology & Medical ResearchXNYS$ 77.110.02$ 4,697,670,000.00$ 100.77$ 26.5645.651.03$ 75.83$ 75.10Stock10.007/11/18$ 23.43$ 40.00$ 234.30$ 771.10$ 536.802.29SELL
13QA9401BIOGEN INC. (XNAS:BIIB)PharmaceuticalsXNAS$ 264.150.02$ 39,595,100,000.00$ 363.92$ 223.2510.630.44$ 259.92$ 258.50Stock29.004/11/17$ 354.31$ 545.00$ 10,274.99$ 7,660.35$ (2,614.64)-0.25HOLD
14JS1983HDFC BANK LIMITED (XNYS:HDB)Banking ServicesTSX$ 79.77-0.03$ 114,892,900,000.00$ 84.70$ 29.5043.730.84$ 82.33$ 80.54Stock12.006/29/17$ 84.32$ 125.00$ 1,011.84$ 957.24$ (54.60)-0.05HOLD
15JS109238REGAL BELOIT CORPORATION (XNYS:RBC)Machinery, Equipment & ComponentsXNYS$ 153.250.03$ 6,054,251,000.00$ 154.96$ 51.9932.181.38$ 149.05$ 148.86Stock4.003/24/17$ 153.45$ 155.00$ 613.80$ 613.00$ (0.80)0.00HOLD
16AG102CANADIAN IMPERIAL BANK OF COMMERCE (XNYS:CM)Banking ServicesXNYS$ 100.760.01$ 55,373,240,000.00$ 101.16$ 46.4513.961.00$ 99.91$ 100.29Stock30.001/3/21$ 90.20$ 150.00$ 2,706.00$ 3,022.80$ 316.800.12HOLD
17AY30981Altria Group, Inc. (XNYS:MO)Food & TobaccoXNYS$ 49.750.01$ 91,893,620,000.00$ 49.95$ 30.9520.500.67$ 49.44$ 49.44Stock5.009/14/18$ 100.23$ 50.00$ 501.15$ 248.75$ (252.40)-0.50HOLD
18AH9381ORACLE CORPORATION (XNYS:ORCL)Software & IT ServicesNASDAQ$ 66.27-0.01$ 192,764,300,000.00$ 73.62$ 41.6615.960.76$ 66.85$ 66.80Stock5.008/9/21$ 43.45$ 50.00$ 217.25$ 331.35$ 114.100.53SELL
19JU67923INTERNATIONAL BUSINESS MACHINES CORPORATION (XNYS:IBM)Software & IT ServicesNASDAQ$ 128.950.01$ 114,594,500,000.00$ 135.88$ 90.5620.491.21$ 128.24$ 128.33Stock10.0010/26/19$ 189.32$ 190.00$ 1,893.20$ 1,289.50$ (603.70)-0.32HOLD
20YT424THE CHILDREN'S PLACE, INC. (XNAS:PLCE)Specialty RetailersXNAS$ 79.66-0.03$ 1,201,392,000.00$ 85.59$ 9.259.522.32$ 82.36$ 81.90Stock10.0011/24/19$ 34.45$ 200.00$ 344.50$ 796.60$ 452.101.31HOLD
21UY3908KINROSS GOLD CORPORATION (XNYS:KGC)Metals & MiningNYSE$ 7.060.03$ 10,810,310,000.00$ 10.32$ 3.536.501.01$ 6.88$ 6.84Stock20.008/24/17$ 15.32$ 30.00$ 306.40$ 141.10$ (165.30)-0.54HOLD
22AW82309COSTCO WHOLESALE CORPORATION (XNAS:COST)Diversified RetailXNAS$ 329.190.01$ 144,819,200,000.00$ 388.07$ 271.3233.470.63$ 327.25$ 326.00Stock30.006/26/19$ 323.45$ 350.00$ 9,703.50$ 9,875.70$ 172.200.02HOLD
23AT120SHOPIFY INC. (XNYS:SHOP)Software & IT ServicesXNYS$ 1,170.860.02$ 141,437,400,000.00$ 1,499.75$ 305.30447.491.46$ 1,143.17$ 1,122.01Stock3.007/20/21$ 802.32$ 825.00$ 2,406.96$ 3,512.58$ 1,105.620.46SELL
24BY7909TARGET CORPORATION (XNYS:TGT)Diversified RetailXNYS$ 178.18-0.01$ 89,855,630,000.00$ 199.96$ 90.1720.840.99$ 180.21$ 179.85Stock93.0012/25/20$ 179.54$ 200.00$ 16,697.22$ 16,570.74$ (126.48)-0.01HOLD
25XT13823902TESLA, INC. (XNAS:TSLA)Automobiles & Auto PartsXNAS$ 701.810.04$ 649,705,600,000.00$ 900.40$ 70.101,088.542.02$ 676.88$ 655.81Stock2.001/11/17$ 702.34$ 850.00$ 1,404.68$ 1,403.62$ (1.06)0.00HOLD
26TY2389Franco-Nevada Corporation (XNYS:FNV)Metals & MiningXNYS$ 124.310.01$ 29,216,790,000.00$ 166.11$ 93.0172.510.50$ 123.37$ 122.75Stock100.0010/1/20$ 125.44$ 155.00$ 12,544.00$ 12,431.00$ (113.00)-0.01HOLD
27JU302982GENERAL MOTORS COMPANY (XNYS:GM)Automobiles & Auto PartsXNYS$ 60.060.05$ 82,304,940,000.00$ 60.12$ 14.3313.221.38$ 57.12$ 57.23Stock5.004/25/20$ 61.34$ 100.00$ 306.70$ 300.28$ (6.43)-0.02HOLD
28LO786WELLS FARGO & COMPANY (XNYS:WFC)Banking ServicesXNYS$ 39.850.01$ 162,677,100,000.00$ 40.44$ 20.7698.711.32$ 39.35$ 39.68Stock20.006/3/20$ 35.43$ 70.00$ 708.60$ 797.00$ 88.400.12HOLD
29AL20897THE GAP, INC. (XNYS:GPS)Specialty RetailersXNYS$ 31.600.01$ 11,654,750,000.00$ 32.95$ 5.2629.901.68$ 31.16$ 31.12Stock10.007/11/21$ 10.34$ 25.00$ 103.40$ 316.00$ 212.602.06SELL
30AT3270BARRICK GOLD CORPORATION (XETR:ABR)Metals & MiningTSX$ 17.430.01$ 45,746,680,000.00$ 26.70$ 11.2816.140.13$ 17.28$ 17.41Stock10.009/3/17$ 453.23$ 500.00$ 4,532.30$ 174.28$ (4,358.02)-0.96HOLD
31TH3810983FORD MOTOR COMPANY (XNYS:F)Automobiles & Auto PartsNYSE$ 12.700.02$ 48,808,960,000.00$ 13.62$ 3.9683.011.21$ 12.49$ 12.38Stock3.004/1/17$ 14.43$ 25.00$ 43.29$ 38.10$ (5.19)-0.12HOLD
Recommendation
Cell Formulas
RangeFormula
S2:S31S2=O2*Q2
T2:T31T2=O2*E2
U2:U31U2=T2-S2
V2:V31V2=(T2-S2)/S2
W2:W31W2=IF(E2>=R2,"SELL","HOLD")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W2:W31Cell Valuecontains "HOLD"textNO
W2:W31Cell Valuecontains "SELL"textNO
F2:F31Other TypeDataBarNO
U2:U32Other TypeDataBarNO
V2:V32Other TypeDataBarNO
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Watch MrExcel Video

Forum statistics

Threads
1,129,527
Messages
5,636,831
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top