Mementodiscpatcher0 9e Exel
Important: Data Catalog Deprecation NoticeData Catalog capabilities in Excel and Power Query stopped working on March 4th, 2019. Users were able to consume content already published to the Data Catalog until that date. However, starting on December 3rd, 2018, users were not be able to publish (share) new or updated queries into the Data Catalog.
Starting on August 1st, 2018, we stopped onboarding new customers (tenants) to use this feature. We recommended downloading your shared queries so you could continue using them outside the Data Catalog beyond March 4th, 2019, from the My Data Catalog Queries task pane, using the Open option for each of the queries.With Power Query, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports.Looking at those steps in order, they often occur like this:.Connect – make connections to data sitting in the cloud, in a service, or locally.Transform – shape the data to meet your needs; the original source remains unchanged.Combine - create a data model from multiple data sources, and get a unique view into the data.Share – once your query is complete, you can save, share or use it for reportsPower Query records each step you take, and lets you modify those steps in any way you need. It also lets you undo, redo, change the order, or modify any step all so you can get your view into the connected data just the way you want it.With Power Query, you can create queries that are as simple or complex as you need.
And since Power Query uses the M Language to record and carry out its steps, you can create queries from scratch (or tweak them manually) to harness the power and flexibility of data scripting, all within Power Query.You can access Power Query from the Power Query ribbon in Excel. Important: If you are using Excel 2013 or earlier versions of Excel, you may need to.
Mementodiscpatcher0 9e Exel File
You can also, which automatically enables it. ConnectYou can use Power Query to connect to a single data source, such as an Excel workbook, or you can connect to multiple databases, feeds, or services scattered across the cloud.
With Power Query, you can then bring all those sources together using your own unique combinations, and uncover insights you otherwise wouldn’t have seen.You connect to data sources from the Power Query ribbon, in the Get External Data section. Data sources include data from the Web, File, Database, Azure, Other Sources, or even Tables in an Excel workbook.The following quick video shows the multitude of data source types to which Power Query can connect. New data connections are being added all the time, so make sure you always have the.When you connect to a data source, a Preview pane will appear. Click Load if you want to work with the data in Excel right away. But if you want to apply transformations or shape the data beforehand, click Edit.
Power Query will then launch the Query Editor: a dedicated window that facilitates and displays your data connections and transformations you apply. The next section, Transform, provides more information about the Query Editor.Learn more about Connect:.TransformPower Query lets you transform the data from your connections in ways that help you analyze it. Transforming data means modifying it in some way to meet your needs – for example, you could remove a column, change a data type, or merge tables – each of which is a data transformation. As you transform data, it collectively takes on the shape you need to further your analysis. The process of applying transformations to one or more sets of data is often called shaping data.Power Query uses a dedicated window called the Query Editor to facilitate and display data transformations.
You can open the Query Editor by selecting Launch Editor from the Power Query ribbon.The Query Editor also opens whenever you connect to a data source, create a new query, or Load an existing query.Power Query keeps track of everything you do with the data. The Query Editor records and labels each transformation, or step, you apply to the data.
Whether the transformation is a data connection (a data source), a column removal, a merge, or a data type change, the Query Editor tracks each operation in the APPLIED STEPS section of the Query Settings pane.The transformations you apply to your data connections collectively constitute your query.It’s important (and helpful) to realize that Power Query doesn’t change the original source data. Instead, Power Query records each step you take when connecting or transforming the data, and once you’ve finished shaping the data, it takes a snapshot of the refined data set and brings it into Excel.There are many, many transformations you can apply to data. You can also write your own transformations using the M Language (which is how Power Query records steps in the background), using Query Editor’s Advanced Editor. You can open the Advanced Editor from the Query Editor’s Transform ribbon, where you can modify the M Language steps associated with the existing query. You can also create queries from scratch using the Advanced Editor.Learn more about Transform:.ShareWhen you save an Excel workbook that contains a query, the query is automatically saved as well. You can view all queries in an Excel workbook by selecting Show Pane from the Workbook Queries section of the Power Query ribbon.The Workbook Queries pane shows all queries in the workbook.But why stop there? With Power Query and the Data Catalog, you can share your queries with anyone in your organization.
Or create a query that you’ll use frequently, then use it in multiple workbooks and save yourself work. Instead of saving and emailing Excel workbooks (and trying to juggle which version is the original, what has changed, or whether its data is stale!), save a query to the Data Catalog and avoid the headache of countless untracked workbook versions filling inboxes. Just right-click on a query in the Workbook Queries pane, and a menu provides all sorts of options, including Send To Data Catalog.Notice the other options in the right-click menu, too.
Nice post, funny - there was a very similar discussion about monte carlo and mandelbrot a few days back, don't know if that's just coincidence?See:The linked workbook is in hi-res so takes a little while, but is worth the wait - maybe someone can help speed it up? (eg using IF(ISNA(.) in the iterations runs about 1/3 quicker)As mentioned there, to improve performance of Monte Carlo, you need to find ways to reduce the variation. In fact, since convergence is order root n, every additional digit of precision requires 100 times more computations!
@ All, Thanx for the words of appreciation.@ LHM, although the Monte Carlo system is in use in both examples, Excel Hero is using the weight of numbers to zero in on an answer and yes the more iterations you do the more accurate it is, as it is a definaite formula that gets you closer to the answer the more you feed into it.In Monte Carlo analysis of variability you never get the right answer, regardless of how many iterations you do. What you do need to do is do enough iterations that the results are statistically accurate. Generally for a Normal Distribution you need a minimum of 30 points to define a statistical population which will be representative of the real population.Because you are generally interested on the probability of the results of the simulation being above/below a certain range, you are more interested in the outliers, than the mean. This means that you need enough outliers to be presented to be a representative population of outliers. For a Normal distribution where +/- 3 Standard Deviations is 99.7% of the population if you then want 30 samples in the remainder 0.3% you need 30 x 100/0.3 or about 10,000 iterations to guarantee a sufficient sample of outliers. This is starting to become a large number and most people run in the 1-10,000 iteration range and then check that sufficient outliers have been presented.
Hui & Chandoo,This was a very instructive post learned a great deal from it.I split out the High and Low in the scenario description and creted a high and low field to hold a percentage and then linked the scenario data to these fileds so that I could change what High and Low meant. If you wanted to test for +10% for high you would put 10% in the High field, or if you wanted to test for a low of 8% you could put -8% in the low field.The possibilities are endless.Thanks once again for the post. @ChrisI ran the Multi Way Tables example with 4 inputs and 5 output variableswith 1,000,000 iterations in the Data Table in about 8 secondsUsing VBA to do 100,000 iterations took about 230 secondsI Deleted all the other sheets to speed it up in both casesExcel easily handles 100,000+ iterations on modestly complex models with easeI try and use the native functions of Excel as opposed to VBA as much as possible, they are just so much faster.The low iteration count in the example models were for demonstration purposes only.
Hui, great post, you and Chandoo really qualify as an ='Excel ' & POWER(Ninja,99) - someday I will get there.I have to say that I agree with your 'Excel can do -almost- anything' statement, I prove it everyday in my workplace.In all my years of working with Excel I´ve learned an important lesson, the very first and ultimate principle that determines the approach to take with an Excel problem: Whatever you can do with the built in Excel functions would run faster and better than if you do the same thing with macros. The problem is that the common mortal doesn´t know what tools he can use, or how to use them.I have to admit that I dont use the Data Table feature but I like´d your way to do the fractal iterations!There is another way to make iterations in Excel. You can use one of the most powerful but concealed feature (and often considered only as an error warning): circular references.
Of course, you have to make more complex formulas, you have to be careful with the physical placement of your variables and calculations, but you can write really complex models with it!Great work, keep the good posts coming!!!!P.D.: Sorry about my english, It´s not my native language! Hi.Montecarlo simulation using excel is amazing. I really appreciate you for sharing the knowledge.I have this question for you.Let us say I am trying to figure out the variation in profit for variable A. Using the method suggested I can run the montecarlo simulation. I want to draw a graph at each iteration (X axis- Different units of variable A), (Y axis - Different profit levels which corresponds to different variable units.)So once the iterations are over a chart will be drawn where this could be used as an efficient frontier.Please let me know whether it will be possible. If this can be done we all have to agree that Excel can do wonders.Many thanks in advance for your support. DilishanYou have 2 easy options here:1.
Scatter Chart - As you have a table of Input Values and results next to it in the actual Data Table, it is a few clicks to chart the data as a scatter chart. You should see more symbols near the mean value and less as you get towards the outliers.2. Cummulative Column Chart - The most common method of Charting Distributions is a Column Chart and associated Cummulative Line Chart (like ).This will require you to setup an area where you have a list of result containers, ie: If your results go from 0 to 100, you may want to setup containers every 5, 0-4.99, 5-9.99,10-14.99 etc and then use a sumifs or sumproduct to count how many times these results are in the Solution part of the Data Table. This is pretty cool.anyone know how to make a data table of a data table?i.e.
I want to run a model for N period where each period is dependent upon the previous period while varying multiple input variables.then i want an outer loop running the above model for M iterations to find the optimal settings for the input variables based on summary statistics found for each iterationwhen i try a data table referencing another data table the referenced data table does not change, so i only get iterations for 1 set of input variables. @Schizophrenia, Rutgerius.For a start thanx for taking the interest in this post.1.
Download the Mandelbrot Spreadsheet from:Don't click on the icon and open it in the web browser.It is an.xls file and will work in all versions of Excel.2. Goto the Calculations Page and select I33.
Press Ctrl Shift Right, Ctrl Shift Down, DeleteThis will select and delete the range I3:DE1034. Go back to H25. Press Ctrl Shift Right, Ctrl Shift Down,This will select the range H2:DE1036. With your mouse got the Data Tab, What If Button and select Data Table7.
In the Data Table dialog enterRow input cell: E5Column input cell: E6.Voila. Hi Chandoo,Congratulations for the great work!I have a couple of questions regarding excel.1) How can I generate auto correlated numbers in excel – Suppose I have fixed the correlation to be 0.7 ( Corr(X,Y) =0.7) then how can I generate random X,Y for it? Is there any way to fix Intra class correlation and generate X,Y for it?2) How a Monte Carlo simulation can be done to test Krippendorff’s Alpha ( statistical parameter) against Intra class Correlation?It would be really thankful for your reply!Thanks! Hui,Thank you so much for this valuable information! I have a question regarding the Monte Carlo Simulation (Simple.) You state, 'The formula =NORMINV(RAND,0.92,0.02), will generate a Random Exchange Rate with a distribution based on a mean on 0.92 A$/U$ and a spread of approximately 6 cents each way ie: there will be a 99.7% probability of the exchange rate being between 0.86 and 0.98 A$/U$.'
My question is: how did you determine the 0.02? I realize that 3 standard deviations will give us the range between 0.86 and 0.98, but how did you determine the 0.02 in the first place? Why not 0.01 or 0.03 or some other number?Thanks! @KathleenThe Norminv function accepts 3 Parameters=NORMINV(probability,mean,standarddev).The 0.92 and 0.02 are the Mean and Standard Deviation that the distribution I used had.In this case the 2 numbers are made up for Demonstration ourposes only.Had I used a SD of 0.02 or 0.2 or 0.3 the Ranges Distribution would change, but it will still have a 99.7% probability of being within the Mean +/- 3 SD's.In reality you will take a number of measurements of your data and then work out what the actual distribution (Mean and Standard Deviation) of the data is.
Hui,first of all, thanks a lot for this post. It is great and what I have been looking for to understand how data tables work.I have a question about 1 way (or 2 way) data tables using percentage variation of inputs. You recommend using formula for input cell (e.g. Gold grade) as =INPUT.1+Temp.Input.Cell. So, using this logic, -50% as in your spreadsheet, gives -15.5 M$ profit which corresponds to 1.18 (i.e. However, for some people, to me at least, -50% more logically means half of the original value, i.e. In this case the formula for the input cell should be =INPUT.(1+Temp.Input.Cell).thanks again for the great help.
In the first 1-Way Table I tried using NormInv to vary the Gold Price using formula =NORMINV(RAND,1.68,0.1). This produces profit results that are completely inconsistent with the input values.The problem is easy to see if the input value is rounded to produce a few discrete values: =ROUND(NORMINV(RAND,1.68,0.1),1). Here is a partial sample output from the table using rounded inputs:5.451.702.101.8010.481.806.291.702.101.806.291.8010.48.How is it that the same input value can produce different outputs?
I'm guessing that somehow the call to volatile Rand causes the Table to evaluate a different value than what is shown. If you look at the output values, they are discrete as well (as expected), but it seems they are mismatched with the inputs.As a test, I tried a different randomizing function for Gold Price =INT(RAND.3)+168. The same problem occurs: the same input can yield different output in different trials.I guess the moral of the story is not to use Rand in the input variable formula.
Has anyone else noticed this behavior? @AndyThank you for pointing this out.There are two things that could be going on here.1. Excel VersionIn Excel versions up to XP Norminv returns errors from time to timeRefer.2. In the first 1-Way Table ,I am trying out the second example of varying the input by percentage. I couldn't quite follow and it doesn't produce the result as you have shown.I have set up the temp cell with a value zero.Changed the Main Input Cell multiplied by 1+ the temp Input Cell.created the data table by giving column input as $c$6. I am not seeing the result as you have shown above. Can you please explain me further?
You can email me separately if this is too basic for others in this thread. Hui,Thanks for an excellent tutorial with great examples. I have been using data tables a lot lately to produce an output page that shows the results from the multi-year model when certain inputs change. Ordinarily, we run the model in real time, demonstrating what happens when we change input #1, etc. Occasionally, we must rely on printouts, and we like a summary sheet of the six scenarios we usually run. Since this is a multi-year model, I generally use a one-variable table and include the profit/loss value for each year as 'monitoring multiple variables' as you describe.
However, I have one scenario where I change two values at once. Since this only happens once, and since I have only one alternate value for each variable, I would love to have a two-way table with monitoring multiple variables. I don't want to use the scenario method you described, because I want to be able to change assumptions in real time - i.e., I don't want to replace those cells with VLOOKUP formulae. Since my final scenario is a combination of two earlier scenarios, I tried what I would call a nested data table - i.e., the output cells are not my profit/loss row, but the results of an earlier data table. In other words, data table #1 changes revenue growth by 5%.
I would like data table # 2 to point to that output and change salary expense to 5% and have the results of data table #2 be a combination of the two.Thoughts?Thomas. Followup - it only seems to be happening with some of my files.They all carry out calcs on various other sheets and the I bring everything back to the same sheet (both inputs and outputs) for the data table, but for some reason it doesn't work.The only way to get it to work is if I close that entire excel window, start a new session with a blank file (or your example file) or a file that I know will work) and then do it that way.Still not sure why its giving the error, but at least I know how to get around it.FWIW, the scnerario manager works just fine as a stand-in. This is truly outstanding, Hui. For some reason i'd missed this post until now.THere is a great article at about combining Data Tables with Advanced Filters and excel's Database formulas (e.g. You can use datatables to 'swap out' the criteria in a DSUM or any other database function. Very very powerful indeed.So you can set up amazing filtering run directly off the spreadsheet in a way that users can easily interact with, and do stuff that would otherwise require SQL.
Blows pivots out of the water, on account that pivots only allow additive filtering. (Of course, you could always feed pivots with disconnected recordsets and generate the requisitie SQL on the fly, but that could get messy)Again, hats off on this amazinf post. Thank you very much for the detailed and though-provoking guide.I have a 'what if' data table I am using to calculate marginal tax rates, using a column of amounts incrementing by, usually, $1000 and using as the what if column variable, different elements of the complete model such as deductions, capital gains, ordinary income, etc.As far as I can see, the cell that was selected to be the column variable is not shown anywhere after the data table is run, or created. Is it possible for my spreadsheet to show what that variable was?
More importantly, I would like some of the calculations I am making to be different (using if formulas) depending on whether the variable that is being incremented is ordinary income, capital gains etc. And to do that I think I need for the if formula to be able to refer to somehow to the cell that was the 'seed' for the resulting data table.Thanks for any help you might offer. Amazing Post Hui.Loved every bit of it.reminded me of a project i built during my Uni using Monte Carlo Simulation Excel for Determining the Number of Counter to be assigned based on service time and arrival of customer in a department store. I just used the rand function there, i wish i cud have read the post earlier so that i could use the Normal Distribution Trick 🙁You made it look so easy 🙂On a serious note a must mention that you are hell of a teacher 🙂 Respect.Note: Please do make a video tutorial of it.the society will be extremely benefited out of it. Learning something new everyday. Thx!One question.
Is it possible to make the Data Table for the What-If Analysis dynamically size? Reason being, I am running a lot scenarios and due to the complexity of the model takes a sufficient amount of time to run each update.
To speed things up, I would like to par down the list of scenarios to run based on certain toggles in the model. Of course, this would only be useful, if the DataTable used for the What-If Analysis can shrink down accordingly. I have already tried turning off the output variables using an if statement if a certain scenario was not applicable but it is still quite slow.Your thoughts are appreciated. Hi thereThis is a valuable post.I have a quick question - any inputs or suggestions would be valuable!I am using a simple data table (1 variable). The variable has a low, base and high values, which are dependent on a user choice in another sheet. So I have used an index/match function to retrieve low/base/high for the variables.
When I use either an index/match, index or offset functions to retrieve these values, the data tables does not update. Is there a way to use volatile excel functions and still get the data tables to work?thanks! @ChaseUnfortunately, Data Tables can't be daisy chained like thatI think it is a deficiency in Excel and is probably made like that to stop Excel freezing on large modelsEven if they would allow 2 levels it would be useful in lots of circumstances.They easiest way around it is to setup a quick look in VBA to Setup the Data TableCopy the value/calculation off to the siderecalculate the Data tableCopy the value/calculation off to the sideetcIf your unfamiliar with VBA I'd be happy to do that for youClick on Hui.
Above, Email is at bottom of page. Hi Hui,Thanks for providing the great and thorough post above! I am fairly new to Excel in terms of these functions. I have a problem I'm working on and I'm unsure where to start due to the number of variables; I think data tables used in Monte Carlo simulation would be appropriate but I'd really appreciate your help.I am trying to estimate/predict the effect of changing customer demand for each terminal of an aiport on the number of air traffic movements for each respective airport terminal, based on five years of previous data.
Eventually, the aim is to see how the number of traffic movements translates into revenue and whether this is likely to increase in the future. I'd like to take into account the time value of money (NPV), both for the historical and future figures.There is also a skew on data for one year due to a special event which I am unsure of how to account for.In terms of generating the particular values for each variable based on past data, would the NORMINV(Randmu,sigma) command be appropriate, once a mean and standard deviation are calculated for the 5 years, or is this too generalised?I would really appreciate any advice you could provide, even if it's just where to start! HiI have a question on how to use nested IF functions instead of data tables where you may have more than 2 variables. Say you are working in cell C9, you have inputs in cells C1:C3, C4 being the calculation. C8, B9 and D6 being the input assumptions used for the sensitivity.I have seen formula like the below used before, but when I try and make one myself I cannot get it to refresh, it just shows 0.=IF(AND($C$1=C$8,$C$2=$B9,$C$3=$D$6),$C$4,C9)Anyone encountered these formulas before?
@MJYou can use any formula's you like but in this case you will only ever get two results, ie: When all conditions are True you will get the result for C4 and if any are false you will get a value using C9The issue is that you have to manually change a cell to get it to recalculate and then record the answer somewhereIf calculation is set to Manual you will also have to force Calculation using F9Data tables get around this by being able to calculate your model for multiple cells with multiple inputs as well as recording the output of multiple cells all at the same time. Hui,Thanks for this fantastic post.
Hi,Thank you so much for the great website. I have found it really useful in my finance classes at B school.Anyway, I am having a problem running this MC sim in excel.
After I run the what if analysis, the new values repeat themselves for the whole tables. The column under 'Growth' is filled with the formula =norm.inv(rand,.03,.04). Below they result in 2%, 5%, etc. The input row cell is hard coded at 3%.
There is no column input. My calc settings are Automatic and enable iterative calculations. I have also tried it as Manual+F9 to no avail.
I am using excel 2013 on Bootcamp.Any ideas on how to prevent the IRR and criteria columns from repeating? Thank you in advance.-JohnBefore# Growth IRR Meets Criteria?1.77% No1 2%2 5%3 6%4 1%After# Growth IRR Meets Criteria?1.77% No1 -3% 1.77% No2 3% 1.77% No3 0% 1.77% No4 3% 1.77% No. Hi Hui,Thank you for this post. I was trying the data table to do monte carlo using VBA to control. The problem I encountered is the same as Ravi mentioned in the comment.If you click anywhere in the excel or in VBE while the data table is running, not-yet-calculated cells will show as 0s (For large data-table or complex model, it's obvious).
The calculation resumed in the background and repopulate results until the calculation is done. I tested with the 6.-Monte-Carlo-Adv.xlsx but set the datatable to Range('F5:K80005').It is hard to automate any processes after the datatable call (e.g., calculating the percentile) because this behavior is unpredictable.Any thoughts on changing settings to force calculation? Would appreciate any suggestions. Thank you.Songn. @RaviUsing Songn as an example and he used Sample 6 as a test:Change the code to:Sub testDim rng As Rangem16 = 'Don't press anything!'
Application.ScreenUpdating = FalseApplication.EnableEvents = FalseSet rng = Range('F5:K80005')rng.Table ColumnInput:=Range('E5')rng.Offset(1, 0).Value = rng.Offset(1, 0).ValueApplication.ScreenUpdating = TrueApplication.EnableEvents = Truem16 = 'Complete'End SubI would also stop users accessing the VBESo add a Button to the worksheet and link the Macro to thatOn my system it is taking less than half a second to run and so I am sure users can wait that longHui. @Hui, thanks so much for this post. I stumbled upon it while searching for a way to represent error bars on an exponential line chart.I'm still in search for a solution to my problem, so I'll start at the start to get your expert opinion:).I have a revenue formula for a business. Let's assume it's for a lemonade stand.