Excel'de İki Analiz: PivotTable Panosu ve Çözücü ile Portföy OptimizasyonuTwo Analyses in Excel: A PivotTable Dashboard and Portfolio Optimization with Solver

Two counterexamples for anyone who thinks Excel is just “where tables live”: a dashboard that explores five years of property sales across 81 provinces with PivotTables and slicers, and a model that finds an optimal portfolio from nine BIST stocks with Solver. I reopened both, verified the numbers — and found one error that needed fixing in each.

1. The property sales dashboard

The first file holds property sales across Turkey's 81 provinces between 2008 and 2012. For each province and year there are three breakdowns: total housing sales, the mortgaged portion, and non-mortgaged (other) sales.

The dashboard side is built with PivotTables: the distribution of sales across regions, the sale-type breakdown per region, and a chart driven by slicers for a selected province and year. Slicers are the most practical way to add interactivity without writing a formula — the user picks a province and a year, and every chart updates together.

Across the five years there are 2,999,283 housing sales, of which 828,878 were mortgaged. The most striking jump in the table is from 2009 to 2010: the mortgaged share climbs from 4.1% to 40.6% and stays at that level for the next two years.

Concentration across provinces

İstanbul and Ankara alone account for 41.9% of housing sales nationwide. The mortgaged share is higher in the big cities too: İstanbul at 34.1% and İzmir at 33.1% sit above the 27.6% average, while Antalya and Kayseri fall below it.

2. The error in the dashboard: region assignments are off

The dashboard's main chart shows sales by region, and it put the Mediterranean region far out in front with 1,013,779 housing sales. That number did not look right — there is no province the size of İstanbul in the Mediterranean region.

I compared the region column in the data against TurkStat's NUTS Level-1 classification. 18 of the 81 provinces are assigned to the wrong region.

The table shows eight of the misassigned provinces; 18 are affected in total. Every one of them falls alphabetically between İstanbul and Mersin. That points straight at the usual cause: one column was sorted on its own, or the region list lost its alignment with the province list. A textbook case of why Excel's “expand the selection?” warning deserves attention.

The impact is not small. Because İstanbul's 734,098 sales were booked to the Mediterranean region, the regional ranking changes completely:

After the correction the lead moves from the Mediterranean to İstanbul, the Aegean more than doubles, and West Marmara drops to a quarter of its reported figure. The total is unchanged (2,999,283) — it simply lands in the right regions.

3. Portfolio optimization with Solver

The second file is a completely different job: optimal portfolio selection with the Konno-Yamazaki model. The classical Markowitz approach measures risk with variance and produces a quadratic problem; Konno-Yamazaki measures risk with mean absolute deviation instead. The practical consequence is that the problem becomes linear and can be solved with Excel's Solver add-in.

The model's result:

The objective value is Z = 509.52. But two rows in that table jump out immediately.

4. Two problems in the portfolio model

A decimal error in the GARAN and HALKB series

GARAN's mean daily return reads −103.27 and HALKB's −33.02, while the other seven stocks sit between −0.35 and +0.53. Looking at the raw data makes the reason obvious: the GARAN column holds values like −85, −431 and −225. A 431% daily fall is impossible — these values are a hundred times too large, meaning the decimal separator was lost (−431 instead of −4.31).

The likely cause: when the data was copied from tr.investing.com, comma decimals did not match Excel's regional settings and the separator was dropped for two stocks. This is one of the most common errors when importing external data, and it happens silently: the cells look numeric, the formulas run, no warning appears.

The target return constraint forces a single solution

The target return constraint in the model is set equal to the highest achievable single-stock return (0.532 ≥ 0.532). With a constraint like that, there is only one thing the optimization can do: put almost all the money into that stock. And indeed the result is 99.49% ARCLK.

That is a consequence of the setup, not of the model. The point of portfolio optimization is to lower risk through diversification; once the target return is pinned to the maximum, there is no room left to diversify. For a meaningful result the target should be lowered and the efficient frontier traced across different targets: computing the minimum risk for each target return and plotting the curve.

5. What these two studies say about Excel

Those two weaknesses are the real argument for doing the same analysis in R or Python. When you write code, the data preparation stays visible step by step, and a mapping like province-to-region becomes a join — one that warns you when a province fails to match. In Excel the same operation is just two columns sitting next to each other, and nothing tells you when the alignment breaks.

Limitations

• The region fix was not written back into the file. The corrected figures above come from a separate check; the dashboard in the repository is still in its original state.
• The property data ends in 2012. The findings hold for that period and do not generalize to today's market.
• The portfolio model rests on 21 trading days. A one-month window is far too short for estimating mean returns; the results are highly sensitive to the sample period.
• No transaction costs or liquidity. The model considers only returns and deviation; commissions, taxes and spreads are not included.
• This is not investment advice. The study is an application of a method; it contains no stock selection or weighting recommendation.

Conclusion

The two files show two different faces of Excel: one a reporting tool that summarizes data and makes it visible, the other a small-scale optimization engine. Both do their job.

The real takeaway is not about the tool. In both files the numbers were computed, the charts were drawn and nothing raised an error — yet in one, half the regions were wrong, and in the other two stocks were inflated a hundredfold. An analysis running is not the same as an analysis being right. Questioning the output is as much a part of the job as producing it.