Excel 2016 automatically updating workbook

19-Nov-2016 20:50

In this post we will learn about how to get stock quotes for specified symbols using macros.

One method that has worked well for my clients can be implemented with just a few lines of VBA code. An Active Range is an area on a worksheet that you define by simply entering the range address in a configuration sheet.

Props","office-ui-fabric-react/lib/utilities/focus","office-ui-fabric-react/lib/components/Check/Check","office-ui-fabric-react/lib/components/Check/Check.scss","office-ui-fabric-react/lib/components/Grouped List/Group Spacer","office-ui-fabric-react/lib/components/Grouped List/Group Spacer.scss","office-ui-fabric-react/lib/components/Details List/Details Header.scss","office-ui-fabric-react/lib/components/Details List/Details Row","office-ui-fabric-react/lib/components/Details List/Details Row Check","office-ui-fabric-react/lib/Check","office-ui-fabric-react/lib/components/Check/index","office-ui-fabric-react/lib/components/Details List/Details Row Fields","office-ui-fabric-react/lib/components/Details List/Details Row.scss","office-ui-fabric-react/lib/Grouped List","office-ui-fabric-react/lib/components/Grouped List/index","office-ui-fabric-react/lib/components/Grouped List/Grouped List","office-ui-fabric-react/lib/components/Grouped List/Grouped List Section","office-ui-fabric-react/lib/components/Grouped List/Group Footer","office-ui-fabric-react/lib/Link","office-ui-fabric-react/lib/components/Link/index","office-ui-fabric-react/lib/components/Link/Link","office-ui-fabric-react/lib/components/Link/Link.scss","office-ui-fabric-react/lib/components/Grouped List/Group Footer.scss","office-ui-fabric-react/lib/components/Grouped List/Group Header","office-ui-fabric-react/lib/Spinner","office-ui-fabric-react/lib/components/Spinner/index","office-ui-fabric-react/lib/components/Spinner/Spinner","office-ui-fabric-react/lib/components/Spinner/Spinner.

Props","office-ui-fabric-react/lib/components/Spinner/Spinner.scss","office-ui-fabric-react/lib/components/Grouped List/Group Header.scss","office-ui-fabric-react/lib/List","office-ui-fabric-react/lib/components/List/index","office-ui-fabric-react/lib/components/List/List","office-ui-fabric-react/lib/components/Grouped List/Grouped List.scss","office-ui-fabric-react/lib/utilities/decorators/with Viewport","office-ui-fabric-react/lib/utilities/decorators/Base Decorator","office-ui-fabric-react/lib/utilities/dragdrop/Drag Drop Helper","office-ui-fabric-react/lib/components/Details List/Details List.scss","office-ui-fabric-react/lib/common/Directional Hint","odsp-next/controls/list/react/React Base List","odsp-next/controls/list/react/details List/fields/Field Map","odsp-next/controls/list/react/details List/fields/Dot Dot Dot Renderer","odsp-next/controls/list/react/details List/fields/Dot Dot Dot Renderer.css","odsp-next/controls/list/react/details List/React Details List.css","odsp-next/controls/list/react/details List/fields/File Type Icon Renderer","odsp-next/controls/list/react/details List/fields/File Size Renderer","odsp-next/controls/list/react/details List/fields/Base Text","odsp-next/controls/list/react/details List/fields/Name Renderer","odsp-next/controls/list/react/details List/fields/Name Renderer.css","odsp-next/controls/list/react/details List/fields/Text Renderer","odsp-next/controls/list/react/details List/fields/Shared Renderer","odsp-next/controls/list/react/details List/fields/User Renderer","odsp-next/controls/list/react/details List/fields/Like Renderer","odsp-next/controls/list/react/details List/fields/Like","odsp-next/controls/list/react/details List/fields/Like.css","odsp-next/controls/list/react/details List/fields/Attachments Renderer","odsp-next/controls/list/react/details List/fields/Url Renderer","odsp-next/controls/list/react/details List/fields/Url Renderer.css","odsp-next/controls/list/react/details List/fields/Image Renderer","odsp-next/controls/list/react/details List/fields/Image Renderer.css","odsp-next/controls/list/react/details List/fields/Lookup Renderer","odsp-next/controls/list/react/details List/fields/Data Bar Renderer","odsp-next/controls/list/react/details List/fields/Data Bar Renderer.css","odsp-next/controls/list/react/details List/fields/Date Renderer","odsp-next/controls/list/react/details List/fields/Date Renderer.css","odsp-next/controls/list/react/details List/fields/Title Renderer","odsp-next/controls/list/react/details List/fields/Title Renderer.css","odsp-next/controls/list/react/details List/fields/Rating Renderer","odsp-next/controls/list/react/details List/fields/Rating","odsp-next/controls/list/react/details List/fields/Rating.css","odsp-next/controls/list/react/details List/fields/Hashtag Renderer","@ms/odsp-utilities/lib/list/Hashtag Utilities","odsp-next/controls/list/react/details List/fields/Taxonomy Renderer","odsp-next/controls/list/react/details List/fields/Workflow Renderer","odsp-next/controls/list/react/details List/fields/Workflow Renderer.css","odsp-next/controls/list/react/details List/fields/Shared With Renderer","odsp-next/controls/list/react/details List/fields/Custom Renderer","odsp-next/controls/list/react/details List/fields/Custom Renderer.css","odsp-next/controls/list/react/details List/fields/Compliance Record Flag Renderer","odsp-next/controls/list/react/details List/fields/Activity Renderer","odsp-next/controls/list Group Header/Base List Header View Model","odsp-next/controls/columns Header/Column Common Utilities","odsp-next/controls/react Deferred Component Wrapper/React Deferred Component","odsp-next/controls/contextual Menu/react/React Contextual Menu","odsp-next/roots/react/Spo Deferred React Root","odsp-next/roots/react/Spo React Root","@ms/odsp-shared-react/lib/Composite Header","@ms/odsp-shared-react/lib/components/Composite Header/index","@ms/odsp-shared-react/lib/components/Composite Header/Composite Header","@ms/odsp-shared-react/lib/components/Composite Header/Composite Header.

In this post, you can learn how to use excel macros to fetch live stock quotes from Yahoo! It is also going to be a crash course in VBA for the express purpose of learning how the Active Range method works so that you can use it yourself.

Click here to download the excel stock quotes macro workbook.

Excel includes the Query Table as one of its core objects, and it is fully addressable from VBA.

Props","office-ui-fabric-react/lib/components/Panel/Panel.scss","@ms/odsp-shared-react/lib/components/Group Membership Menu/Group Membership Menu","@ms/odsp-shared-react/lib/components/People Picker/People Picker","office-ui-fabric-react/lib/Pickers","office-ui-fabric-react/lib/components/pickers/index","office-ui-fabric-react/lib/components/pickers/Suggestions/Suggestions","office-ui-fabric-react/lib/components/pickers/Suggestions/Suggestions.scss","office-ui-fabric-react/lib/components/pickers/Suggestions/Suggestions Controller","office-ui-fabric-react/lib/components/pickers/Auto Fill/Base Auto Fill","office-ui-fabric-react/lib/components/pickers/Base Picker","office-ui-fabric-react/lib/components/pickers/Base Picker.scss","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker Items/Selected Item Default","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker Items/Picker Items Default.scss","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker Items/Suggestion Item Default","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker Items/Selected Item With Menu","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker.scss","office-ui-fabric-react/lib/components/pickers/Tag Picker/Tag Picker","office-ui-fabric-react/lib/components/pickers/Tag Picker/Tag Item","office-ui-fabric-react/lib/components/pickers/Tag Picker/Tag Item.scss","@ms/odsp-datasources/lib/People Picker","@ms/odsp-datasources/lib/data Sources/people Picker/People Picker Data Source","@ms/odsp-datasources/lib/data Sources/base/Data Source","@ms/odsp-datasources/lib/data Sources/base/Data Requestor","@ms/odsp-datasources/lib/data Sources/base/Server Data","@ms/odsp-datasources/lib/data Sources/base/Server Connection","@ms/odsp-utilities/lib/logging/rumone/APICall Performance Data","@ms/odsp-utilities/lib/logging/rumone/RUMOne Logger","@ms/odsp-utilities/lib/logging/rumone/Page Transition Type","@ms/odsp-utilities/lib/logging/rumone/RUMOne SLAPI","@ms/odsp-utilities/lib/logging/rumone/RUMOne Errors SLAPI","@ms/odsp-utilities/lib/logging/events/RUMOne Data Upload.event","@ms/odsp-datasources/lib/data Sources/people Picker/IPerson","@ms/odsp-datasources/lib/data Sources/people Picker/IPresence Type","@ms/odsp-datasources/lib/providers/people Picker/People Picker Provider","@ms/odsp-datasources/lib/models/store/People Store","@ms/odsp-datasources/lib/data Sources/role Assignments/Principal Type","@ms/odsp-shared-react/lib/components/People Picker/People Picker Default Items","@ms/odsp-shared-react/lib/components/People Picker/People Picker.

We then need to figure out what to do with the CSV file that comes back.

Our VBA will take care of that and manage the Active Range.

Once enabled, that range becomes live in the sense that if you add or change a stock symbol in the first column of the range, the range will automatically (and almost instantly) update.

You can specify any of 84 information attributes to include as columns in the Active Range.

Props","office-ui-fabric-react/lib/components/Panel/Panel.scss","@ms/odsp-shared-react/lib/components/Group Membership Menu/Group Membership Menu","@ms/odsp-shared-react/lib/components/People Picker/People Picker","office-ui-fabric-react/lib/Pickers","office-ui-fabric-react/lib/components/pickers/index","office-ui-fabric-react/lib/components/pickers/Suggestions/Suggestions","office-ui-fabric-react/lib/components/pickers/Suggestions/Suggestions.scss","office-ui-fabric-react/lib/components/pickers/Suggestions/Suggestions Controller","office-ui-fabric-react/lib/components/pickers/Auto Fill/Base Auto Fill","office-ui-fabric-react/lib/components/pickers/Base Picker","office-ui-fabric-react/lib/components/pickers/Base Picker.scss","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker Items/Selected Item Default","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker Items/Picker Items Default.scss","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker Items/Suggestion Item Default","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker Items/Selected Item With Menu","office-ui-fabric-react/lib/components/pickers/People Picker/People Picker.scss","office-ui-fabric-react/lib/components/pickers/Tag Picker/Tag Picker","office-ui-fabric-react/lib/components/pickers/Tag Picker/Tag Item","office-ui-fabric-react/lib/components/pickers/Tag Picker/Tag Item.scss","@ms/odsp-datasources/lib/People Picker","@ms/odsp-datasources/lib/data Sources/people Picker/People Picker Data Source","@ms/odsp-datasources/lib/data Sources/base/Data Source","@ms/odsp-datasources/lib/data Sources/base/Data Requestor","@ms/odsp-datasources/lib/data Sources/base/Server Data","@ms/odsp-datasources/lib/data Sources/base/Server Connection","@ms/odsp-utilities/lib/logging/rumone/APICall Performance Data","@ms/odsp-utilities/lib/logging/rumone/RUMOne Logger","@ms/odsp-utilities/lib/logging/rumone/Page Transition Type","@ms/odsp-utilities/lib/logging/rumone/RUMOne SLAPI","@ms/odsp-utilities/lib/logging/rumone/RUMOne Errors SLAPI","@ms/odsp-utilities/lib/logging/events/RUMOne Data Upload.event","@ms/odsp-datasources/lib/data Sources/people Picker/IPerson","@ms/odsp-datasources/lib/data Sources/people Picker/IPresence Type","@ms/odsp-datasources/lib/providers/people Picker/People Picker Provider","@ms/odsp-datasources/lib/models/store/People Store","@ms/odsp-datasources/lib/data Sources/role Assignments/Principal Type","@ms/odsp-shared-react/lib/components/People Picker/People Picker Default Items","@ms/odsp-shared-react/lib/components/People Picker/People Picker.We then need to figure out what to do with the CSV file that comes back.Our VBA will take care of that and manage the Active Range.Once enabled, that range becomes live in the sense that if you add or change a stock symbol in the first column of the range, the range will automatically (and almost instantly) update.You can specify any of 84 information attributes to include as columns in the Active Range.Props","@ms/odsp-shared-react/lib/components/People Picker/Picker Item.scss","@ms/odsp-shared-react/lib/components/Group Membership Panel/Group Membership Panel.scss","odsp-next/controls/group Membership Panel/Group Membership Panel Container","@ms/odsp-shared-react/lib/containers/group Membership Panel/index","@ms/odsp-shared-react/lib/containers/group Membership Panel/Group Membership State Manager","@ms/odsp-datasources/lib/Groups","@ms/odsp-datasources/lib/providers/groups/Groups Provider","@ms/odsp-datasources/lib/models/groups/Group","@ms/odsp-datasources/lib/models/groups/Membership","@ms/odsp-datasources/lib/data Sources/groups/Members List","@ms/odsp-datasources/lib/interfaces/groups/Source Type","@ms/odsp-datasources/lib/data Sources/groups/Groups Data Source","@ms/odsp-datasources/lib/data Sources/base/Data Batch Operation Helper","@ms/odsp-datasources/lib/Acronym And Color","@ms/odsp-datasources/lib/data Sources/site Header/Acronym And Color Data Source","@ms/odsp-datasources/lib/interfaces/ISp Page Context","@ms/odsp-datasources/lib/data Sources/base/Cached Data Source","@ms/odsp-shared-react/lib/Site Permissions Panel","@ms/odsp-shared-react/lib/components/Site Permissions Panel/index","@ms/odsp-shared-react/lib/components/Site Permissions Panel/Site Permissions Panel","@ms/odsp-shared-react/lib/components/Site Permissions/Site Permissions","@ms/odsp-shared-react/lib/components/Site Permissions/Site Permissions Menu","@ms/odsp-shared-react/lib/components/Site Permissions/Site Permissions.scss","@ms/odsp-shared-react/lib/components/Site Permissions Panel/Site Permissions Panel.scss","odsp-next/controls/site Permissions Panel/Site Permissions Panel Container","@ms/odsp-shared-react/lib/containers/Site Permissions/Site Permissions State Manager","@ms/odsp-datasources/lib/Site Permissions","@ms/odsp-datasources/lib/data Sources/role Assignments/ISPUser","@ms/odsp-datasources/lib/data Sources/role Assignments/Site Permissions Data Source","@ms/odsp-datasources/lib/providers/site Permissions/Site Permissions Provider","@ms/odsp-shared-react/lib/List Creation Panel","@ms/odsp-shared-react/lib/components/List Creation Panel/index","@ms/odsp-shared-react/lib/components/List Creation Panel/List Creation Panel","@ms/odsp-shared-react/lib/components/List Creation Panel/List Creation Panel.scss","@ms/odsp-shared-react/lib/components/List Creation Panel/List Creation Panel Content","office-ui-fabric-react/lib/Text Field","office-ui-fabric-react/lib/components/Text Field/index","office-ui-fabric-react/lib/components/Text Field/Text Field","office-ui-fabric-react/lib/Label","office-ui-fabric-react/lib/components/Label/index","office-ui-fabric-react/lib/components/Label/Label","office-ui-fabric-react/lib/components/Label/Label.scss","office-ui-fabric-react/lib/components/Text Field/Text Field.scss","office-ui-fabric-react/lib/Checkbox","office-ui-fabric-react/lib/components/Checkbox/index","office-ui-fabric-react/lib/components/Checkbox/Checkbox","office-ui-fabric-react/lib/components/Checkbox/Checkbox.scss","@ms/odsp-shared-react/lib/containers/Edit Nav/index","@ms/odsp-shared-react/lib/containers/Edit Nav/Edit Nav State Manager","@ms/odsp-shared-react/lib/Edit Nav","@ms/odsp-shared-react/lib/components/Edit Nav/index","@ms/odsp-shared-react/lib/components/Edit Nav/Edit Nav","@ms/odsp-shared-react/lib/components/Edit Nav/Edit Nav Context Menu/Edit Nav Context Menu","@ms/odsp-shared-react/lib/components/Edit Nav/Edit Nav Context Menu/Edit Nav Context Menu.scss","@ms/odsp-shared-react/lib/components/Edit Nav/Edit Nav Callout/Edit Nav Callout","office-ui-fabric-react/lib/Focus Trap Zone","@ms/odsp-shared-react/lib/components/Edit Nav/Edit Nav Callout/Edit Nav Callout.scss","@ms/odsp-shared-react/lib/components/Edit Nav/Edit Nav.scss","@ms/odsp-shared-react/lib/components/Edit Nav/Edit Nav Data Cache","@ms/odsp-datasources/lib/Edit Nav","@ms/odsp-datasources/lib/data Sources/edit Nav/Edit Nav Data Source","@ms/odsp-datasources/lib/data Sources/edit Nav/View Nav Data Source","@ms/odsp-datasources/lib/data Sources/edit Nav/IEdit Nav Data Source","@ms/odsp-shared-react/lib/Site Settings Panel","@ms/odsp-shared-react/lib/components/Site Settings Panel/index","@ms/odsp-shared-react/lib/components/Site Settings Panel/Site Settings Panel","office-ui-fabric-react/lib/Dropdown","office-ui-fabric-react/lib/components/Dropdown/index","office-ui-fabric-react/lib/components/Dropdown/Dropdown","office-ui-fabric-react/lib/components/Dropdown/Dropdown.scss","@ms/odsp-shared-react/lib/components/Site Settings Panel/Site Settings Panel.scss","odsp-next/controls/site Settings Panel/Site Settings Panel Container","@ms/odsp-shared-react/lib/containers/site Settings Panel/index","@ms/odsp-shared-react/lib/containers/site Settings Panel/Site Settings Panel","@ms/odsp-datasources/lib/Group Site","@ms/odsp-datasources/lib/providers/groups/Group Site Provider","@ms/odsp-datasources/lib/data Sources/groups/IGroup Site Info","@ms/odsp-datasources/lib/data Sources/groups/Group Site Data Source","@ms/odsp-shared-react/lib/containers/list Creation Panel/index","@ms/odsp-shared-react/lib/containers/list Creation Panel/List Creation Panel Container State Manager","@ms/odsp-datasources/lib/List Collection","@ms/odsp-datasources/lib/data Sources/list Collection/ISPList Creation Information","@ms/odsp-datasources/lib/data Sources/list Collection/List Template Type","@ms/odsp-datasources/lib/data Sources/list Collection/SPList Collection Data Source","@ms/odsp-shared-react/lib/components/Group Card/Group Card","office-ui-fabric-react/lib/components/Command Bar/index","office-ui-fabric-react/lib/components/Command Bar/Command Bar","office-ui-fabric-react/lib/components/Command Bar/Command Bar.scss","@ms/odsp-shared-react/lib/components/Members Info/Members Info.