You could also perform far more advanced analyses (which I’ll discuss in future blog posts). For example, I’ve used this variations of this spreadsheet to calculate investment ratios (e.g. Sharpe, Sortino and Treynor Ratios), plot histograms of daily returns to check if they’re normally distributed, identified stocks with my desired risk characteristics, and calculate mean-variance optimal portfolios. The VBA that retrieves the data from Yahoo was adapted from code found at. It can be modified to download other data found on the web. You’ll need to enable macros before you use the spreadsheet (Excel may warn you that you’re importing a macro-enabled worksheet). UPDATE July 17th 2011: Here’s an UPDATE 18th August 2011: Here’s a UPDATE 14th February 2013: UPDATE 5th January 2014: I’ve just evaluated a financial data add-in for Excel. It’s a rock-solid route to getting live stock data inside Excel. You just use one of 80 new functions together with your chosen ticker. Disclaimer: the link to is an affiliate link, but this has not influenced my opinion of the tool. Excel 2016 for Mac supports ODBC data connections with SQL Server and Azure SQL Database right out of the box. This means several great things for anyone who works with external data: When creating or refreshing data connections to SQL Server, there are no third-party drivers required—everything you need is included right in the app. Connections made to SQL Server in Excel 2016 for Mac will work in Excel for Windows and vice versa. One click will automatically convert any JSON URL into tabular format with column headers and populate the worksheet cells with the new data. A timer can be set up to automatically re-fill the cells periodically. This allows you to effectively set up a live feed of API data. You can choose to set up the feed in a separate sheet. ![]() UPDATE July 6th 2017: The old Yahoo Finance API was discontinued, so the spreadsheet stopped working for a while. However, I’ve now uploaded a new working version. To create a live connection to the reporting API on a PC: 1. Open Excel 2016 and create a blank workbook. Click on 'Data' → 'New Query' → 'From Other Sources' → 'From Web' 3. Ensure that the 'Basic' radio button is selected in the dialog that appears, paste your generated URL from the Reporting API URL Generator into the 'URL' field, and click 'OK' 4. The 'Access Web Content' dialog should appear, with the 'Anonymous' tab selected. Click 'Connect' 5. A 'Connecting' dialog will appear while the request is made. Ensure that the data appears to have been parsed correctly on the resulting dialog, and if it is click the 'Load' button 6. Your data should now be loaded into a new sheet in your Excel workbook. You can then periodically click the 'Refresh All' button to get updated data, as long as your token hasn't expired. If the token does expire, simply edit/update the query URL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2019
Categories |