Which brings me to R. R can do all of that and more and there is much more community backing.
).There is a subtle detail missing in the Historical section.
That makes is a more difficult task to populate in my spreadsheet.
A 50 day moving average should be the sum of the last 50 data points divided by 50. You can just change the Type in the FieldLookup table to text which will stop the error but you wont be able to sort the field numerically.Note once you have data in an Excel Table like this, it’s easy to add additional calculated columns into the table auto update when the query does.So for now I’m going to turn off the Formula Firewall.So I’ll crack that nut next time.Every column that has a “true” in the Display column of FieldLookup table will get a column returned with data. The “Type” column is used to encourage Excel to use the right column type when displaying (everything will still work using the default Excel column type, but Numeric and Date columns wont sort nor filter properly).Now Close and Load the Query. Now go to File=>Options and Settings=>Query Options and click on Privacy. I create my own MAV using a calculation in the table.very impressive! Close and Load that query into an Excel table called Historical. It looks like this. Thanks again for posting this!!! Manage your holdings efficiently and create custom views using over 60 data points. Rick.Pulling this data into Power Pivot is really easy from which it’s even easier to post process and report.Yes I think Yahoo Finance is wrong there. The jurry is still out on M, especially via Excel, especially since there are other, much more open Extract/Transform/Load options availalbe for various and sundry data types when you toss Excell out the window to begin with. Save them as backups or for analysis in spreadsheets. By selecting Public on these queries you are declaring that you are happy to send your stock portfolio to Yahoo.Now in a new Excel sheet and add table called Portfolio with a single column Symbol and add some stock code symbols. The New Query button has a huge number of different data sources. I put both the Historical and the HistoricalParameters tables on the same Worksheet so you can update the Symbol and the Days and see the result. That is until this latest version of Office where you can’t really avoid it. And not being able to connect to a dbf file (when most of the GIS world uses them daily) is pretty arrogant, especially given that M$FT purchased FoxPro. Then each query will get a “Formula.Firewall” warning. Thank you.Ah, now we get our first glimpse of M. The Web import wizard created a set of M code that queried the URL, determined the type of data returned (csv), determined the first row is headers and had a guess at the column types of the data and returned that data as a Table.Oh yeah! Very much appreciated the great write up, comments about M, DAX, R, open software support, etc. And use that to call our fHistorical function. These APIs are simple web queries but predate the REST API conventions so don’t follow nice RESTful best practice.