Presentation Transcript

1. Connecting to External Data

2. Financial data can be obtained from a number of different data sources
3. A text file contains only text and numbers without any formulas, graphics, special fonts, or formatted text Text files typically have a that is used to organize the data within the file Typically a
4. Another way to organize text is a fixed-width text file where each column starts in the same location in the file (looks a lot like a tab separated file) Even though you might havenever seen the contents of the text file, you can easilyimport it into Excelusing the text import wizard
5. Information contained within a database can also be imported into Excel Information in a database is stored in the form of: T Tables contain characteristics of the data. Those are called A Tables are linked together based upon? A
6. Common fields allow you to from multiple tables. A large database may contain dozens or hundreds of tables. Each table can have many different fields and each table may contain thousands of records. When you want to look at only specific information found in a database, what will you create? A
7. Importing database data Excel supports two ways of importing database data. Create a connection to a database and import all of the data from a single table. If you need to retrieve data from multiple tables, use the query wizard Select tables and fields from tables Filter and sort data
8. Web Query If the data that you want to import is on a web page somewhere, you can create a web query to import data. A web query specifies how data should be retrieved from a web page and it indicates exactly what parts of the web page should be included and which parts should be excluded. Info for Apple Computers http://finance.yahoo.com/q/hp?s=AAPL Historical prices Insider transactions Key statistics
9. Connecting to and retrieving external data Connect to text files Connect to database files Connect to real-time stock and mutual fund data using refreshable stock quotes Import data from a web page using a web query Import data from XML files using XML technologies
10. XML (extensible markup language) HTML vs XML HTML how data should be displayed on a web site. interpret HTML code when displaying web sites. HTML was designed to and to focus on XML tags indicate . You can . XML was designed to <CompanyName> Apple </CompanyName> <TickerSymbol> AAPL </TickerSymbol>
11. XML Technologies Instance documents Schemas Stylesheets describe
12. Core XML Technologies The XML Instance document holds data Stores data in a hierarchical format XML Instance documents are the heart of XML. XML Schemas Defines the structure of a valid XML instance document and the types of values that elements may hold. XML Schemas are used to validate the XML instance documents. Someone who has your schema knows what the information that you will be sending them or exchanging with them will look like. Schemas define valid documents (what they look like and what they must contain) XSL (the extensible Stylesheet Language) Describes how the XML data should be displayed Used to convert an XML instance document from one format to another.
13. An XML Instance Document <?xml version=“1.0”?> <SelectedBig12Universities> <University> <name>Kansas State University</name> <city>Manhattan</city> <state>Kansas</state> <mascot>Wildcat</mascot> <abbreviation> KSU</abbreviation> <color> Purple</color> </University> <University> <name> University of Kansas</name> <city>Lawrence</city> <state>Kansas</state> <mascot> Jayhawk </mascot> <abbreviation> KU</abbreviation> <color> Blue</color> </University> </SelectedBig12Universities> Follows a hierarchical format that you can expand or collapse Link
14. Inventory Schema A description of the rules that“valid” XMLinstance documentsmustfollow. Applicationsthat understanda schema know what to expect and can process any associated, valid documents. Schemas define the content, organization, and structure of valid instance documents.
15. Understanding XML More and more of today’s software applications are able to understand XML and serve as XML processors Web browsers such as Internet Explorer and FireFox The programs in Microsoft Office were able to understand XML starting with Office XP Starting with Office 2007, Microsoft changed the format that files were saved as to the Office Open XML format. .xlsx, .docx, etc.
16. Files and data tagged using one of the XML formats (including XBRL: Search for, retrieve and manipulate tagged data that meets your specific business needs. When you use Excel to connect to an XML document, Excel creates a data map of the document’s structure and contents. If a schema is present, then it is used to create a data map. If a schema is not present, then Excel creates an inferred schema based on the contents of an instance document.
17. The allows you to access Excel’s XML functionality. File, Options, Customize Ribbon, Check to Show Developer in Ribbon You can use the data map to attach orbind XML data to specific cells found inyour Excel spreadsheet. If the XML data has been changed, then refreshing the spreadsheet willautomatically import the data values into your spreadsheet.
18. Populating an XML instance document with data from Excel Create an instance document skeleton containing Import the instance document skeleton into Excel and bind cells spreadsheet to the skeleton’s data map. Data may already exist in cells to be bound. Bound cells can be specified and then data entered in Save bound data as XML data, using instance document name. Refresh browser and instance document should be populated with data from Excel. Both files tied together (changes in one reflected in other)

19. XML

    Extensible Markup Language
20. Language of Love for Mutual FundsXBRL (eXtensible Business Reporting Language) XBRL is one of the XML schemas (a specific set of rules that documents of a certain type must follow) If your organization is required to report data to a regulatory body, they will dictate the rules that you have to follow when doing your reporting. That is where schemas come into play. If data has been “tagged” using XML/XBRL, then it is very easy to pull relevant data from long reports/filings, and then be able to analyze and compare data across many different sources. After some sort of computer program is created, with the click of a mouse, an XML processor can retrieve tagged data from whatever data repository it is in
21. The XSL Stylesheet <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:template match = "/"> <HTML> <TABLE BORDER = "1"> <xsl:for-each select="inventory/item"> <TR> <TD> <xsl:value-of select="."/> </TD></TR> </xsl:for-each> </TABLE> </HTML> </xsl:template> </xsl:stylesheet> Used to format instance documents
22. XSL Stylesheet <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:template match = "/"> <HTML> <TABLE BORDER = "1"> <TR> <TH>Item</TH> <TH>Cost</TH> <TH>QOH</TH> <TH>TOTAL</TH></TR> <xsl:for-each select="inventory/item"> <TR> <TD> <xsl:value-of select="name"/> </TD> <TD> <xsl:value-of select="cost"/> </TD> <TD> <xsl:value-of select="qoh"/> </TD> <TD> <xsl:value-of select="cost * qoh"/> </TD></TR> </xsl:for-each> </TABLE> </HTML> </xsl:template> </xsl:stylesheet> 5 row, 4 column table with multiple cells
23. Example of Instance Document Coding
24. WF Well-Formed XML Instance Documents Well-formed XML documents can be processed by an XML processor. Internet Explorer can serve as an XML processor. XML is also a feature built into Microsoft Office (beginning with XP) A well-formed XML document obeys all of the XML syntax rules. There is one and only one root element. Tags must balance – every opening tag must have a closing tag. XML is case sensitive – upper and lowercase characters are different. Opening and closing tags must be the same, except the closing tag starts with a slash (/). NWF
25. XML SummaryExtensible Markup Language HTML was designed to display data and to focus on how data looks. XML was designed to describe data and to focus on what data is. XML Technologies Instance documents hold data. Schemas define valid documents (what they look like and what they must contain) Stylesheets describe how to format the data. XML is the new foundation/standard for exchanging business documents electronically.
26. XML: Extensible Markup Language HTML was designed to display data and to focus on how data looks. XML was designed to describe data and to focus on what data is. Extensible: that which can be extended, it is flexible, and can be added to. XML adds meaning or context to the data that is being transmitted. <weight>100</weight> HTML is used to display documents with a web browser, but HTML cannot be used by other applications because it does not add any INFORMATION about the data being transmitted.